Ranking Days in week excluding holidays from a holiday lookup / get working days of week

[Anonymous]

Hi Everybody,

I have a date table and a hoilday lookup table. I need to get the workingday of the date and when its a weekend or holiday, the number from the last working day should be used. In the example screenshot you can see that I already have this column that ranks the workingdays of the month - I would need this per week. So Mon - Fri are numbered 1-5 and Sat + Sun will also be numbered 5 and on monday it starts with 1 again. I need this as a calculated column - not only a measure.

Here's the DAX that was used for the ranking of days per month:

number_wd_exAB2 =

VAR _date='_Date_LookUP'[Date]

VAR month= MONTH('_Date_LookUP'[Date])

VAR year= YEAR('_Date_LookUP'[Date])

RETURN

RANKX(

    FILTER('_Date_LookUP', IF([DayOfWeek]=5 || [DayOfWeek]=6 || ISBLANK([which_holiday])=FALSE(),1,0)=0

    && MONTH('_Date_LookUP'[Date])=month

    && YEAR('_Date_LookUP'[Date])=year

    )

,'_Date_LookUP'[Date],,ASC

)

Can someone help? Performance is not important at that point, the calculation will be done on a database in the future but I still need it now.

Thanks,

Michella

[Anonymous]

Hi @v-xinruzhu-msft, thanks for the help!

[v-xinruzhu-msft · Accepted solution]

Hi @Anonymous 

You can refer to the following example

Create a new calculated column

Rank = WEEKDAY([Date],2)-COUNTROWS(FILTER(_Date_LookUP,OR([which_holiday]<>BLANK(),[Day of Week] in {6,0})&&[Date]<=EARLIER(_Date_LookUP[Date])&&([Date]-WEEKDAY([Date],2)+1)=(EARLIER(_Date_LookUP[Date])-WEEKDAY(EARLIER([Date]),2)+1)))

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.