cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

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

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

1 ACCEPTED SOLUTION
Community Support

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.

2 REPLIES 2
Anonymous
Not applicable

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

Community Support

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.