Calculated Column for first 5 working days in each month

1
votes

I wanted to do calculated column in my calendar table in Power BI. The calculated column should show "1" for the first 5 working days in each month in the calendar table, the rest of the days should be "0" . I tried to come out with the formula shown below

tick = CALCULATE(COUNT('Calendar'[Weekend - weekday]), 'Calendar'[IsWorkingDay] = TRUE)

Calendar table

But it shows "1" for all the working days but the desire output is the first 5 working days of each month. Anyone could help me

1
powerbidaxcalculated-columns

1 Answers

0
votes

Suppose this is your original table with new column for Weekday-weekend, you can calculate the new column to display 0 for first five working days using rankx & If, followed by 0 and blank for other case, here is the dax formula:

Remark: Both 1 and 0 need to with quotation mark, as using with "" without convert to string will cause Data is variant type error.

tick1 = 
var rank1 = RANKX(FILTER(Sheet1,Sheet1[Weekday-Weekend] = "Weekday" && Sheet1[Period] = EARLIER(Sheet1[Period]) ),Sheet1[Day],,ASC)
return
IF(Sheet1[Weekday-Weekend] = "Weekday" && rank1 >=1 && rank1 <=5, "1", 
    IF(Sheet1[Weekday-Weekend] = "Weekday", "0",""))

The table with add column

enter image description here