cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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.

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors