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..
Hi v-robertq-msft ,
Tried it right away :), but i'm still running into something here.
Here are my columns:
Date:
Workday identifier:
This bit is clear and works fine.
But....
When the shipping date is after the promise date the result seems correct.
However when the shipping is before the promise date, the result is empty. Probably because the sum function can't find anything to some based on the conditions.
But the reality is (fortuantely) that most shipments are delivered on time, so negative values are also needed to determine how early the shipment is delivered. You can imagine that customer don't want their goods too early. We have a limit for that as well.
Parhaps I can mix your fix into a countrows instead of a sum and see if that works.
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! :):):)