Forum Discussion

PaulusD's avatar
PaulusD
Resolver II
5 years ago
Solved

Calculating working days

Hello,   I've read numerous threads on calculating working days, but still aren't getting it working at the moment.. I have: a date table (marked as date table) a column IsWorkingDay ...
Need Help
  • PaulusD's avatar
    PaulusD
    5 years ago

    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! :):):)

     

v-robertq-msft's avatar
v-robertq-msft
Community Support
5 years ago

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:

 

  1. Create a calendar table:
Date = CALENDARAUTO()
  1. Create a calculated column in the calendar table:
Isworkday =

IF(WEEKDAY([Date],2)>5,0,1)
  1. Don’t give them a relationship:

 

  1. 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.