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, Anonymous 

According to your description of your sample data, I created another two rows of data and transformed the DAX formula to remove the blank value:

 

 

Workdays Count =

IF(

    [Start Date]<=[End Date],

    CALCULATE(SUM('Date'[Isworkday]),FILTER(ALL('Date'),'Date'[Date]>=EARLIER('Fact table'[Start Date])&&'Date'[Date]<=EARLIER('Fact table'[End Date]))),

    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 the expected output:

 

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.