Forum Discussion
Calculating working days
v-robertq-msft , I think I've found it!
The date table and workingday column are the samen, but the working days delta has become the following:
as you can see, the one delayed shipment in the overview now gets a positive number. I should be able to use this to measure delayed shipments within a tolerance finally get the performance based on working days! :):):)
Hi,
According to your description, I can roughly understand your requirement, I think you can simply achieve this using a calendar table, a calculated column, and a measure:
Create a calendar table:
Date = CALENDAR(DATE(2021,1,1),DATE(2021,12,31))
Create a calculated column:
Isworkday =
IF(WEEKDAY([Date],2)>5,0,1)
Then create a measure:
Count of workdays =
CALCULATE(SUM('Date'[Isworkday]),ALLSELECTED('Date'))
Then you can create a slicer and a card chart to place them like this:
And you can get what you want.
You can download my test pbix file below
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi v-robertq-msft ,
Thanks for your help.
Create a calendar table:
Date = CALENDAR(DATE(2021,1,1),DATE(2021,12,31))
This part is clear. I used the calendar auto function, but that shouldn't make any difference.
Create a calculated column:
Isworkday =
IF(WEEKDAY([Date],2)>5,0,1)
This part is also clear.
The following part might not work fully.
Then create a measure:
Count of workdays =
CALCULATE(SUM('Date'[Isworkday]),ALLSELECTED('Date'))
Then you can create a slicer and a card chart to place them like this:
I understand what you've done though.
But i'm not trying to calculate working days based on a slicer, I'm trying to calculate working days between two dates in a seperate fact table.
For the majority of dates it seems to work, however when the dates spand over year's the result is inaccurate.
Is this clear for you? or can I do anything else to elaborate?
Thanks.