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 in detail, I can roughly understand your requirement now, and I updated my method, you can take a look and check if this can be helpful:
This is the test fact table I created:
- Create a calendar table:
Date = CALENDARAUTO()- Create a calculated column in the calendar table:
Isworkday =
IF(WEEKDAY([Date],2)>5,0,1)- Don’t give them a relationship:
- reate a calculated column in the fact table:
Workdays Count =
CALCULATE(SUM('Date'[Isworkday]),FILTER(ALL('Date'),'Date'[Date]>=EARLIER('Fact table'[Start Date])&&'Date'[Date]<=EARLIER('Fact table'[End Date])))
And you can get what you want, like this:
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 ,
Now that's an approach I haven't tried yet :).
I get around to this today and let you know if it works.
Just out of curiousity, is that datetable marked as the date table?
And why is there no relationship between the two tables?
Will get back to you a.s.a.p..