cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PaulusD
Helper I
Helper I

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

PaulusD_0-1623998499562.png

  • a data table.
  • calculated column for the difference in working days

PaulusD_1-1623998718780.png

 

Now some rows are returning values which seem to be fine, and some rows are blank.

The blank rows are in cases where the dates being measured are the same (in which case I would expect a 0), but also in cases where they differ (in which case i "simply" need a result).

 

Can anyone help me out with this?

 

Thanks for the support.

 

ps., i'm afraid i cant share the .pbix...

 

1 ACCEPTED SOLUTION

@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:

PaulusD_0-1625119760150.png

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

 

View solution in original post

13 REPLIES 13
v-robertq-msft
Community Support
Community Support

Hi, @tqn626 

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:

vrobertqmsft_0-1625120670707.png

 

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.

v-robertq-msft
Community Support
Community Support

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:

vrobertqmsft_0-1625111268936.png

 

  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:

vrobertqmsft_1-1625111268940.png

 

  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:

vrobertqmsft_2-1625111268941.png

 

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:

PaulusD_1-1625116704178.png

 

Workday identifier:

PaulusD_0-1625116661071.png

This bit is clear and works fine.

 

 

But....

PaulusD_2-1625116885797.png

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:

PaulusD_0-1625119760150.png

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

 

View solution in original post

v-robertq-msft
Community Support
Community Support

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:

vrobertqmsft_0-1625042593505.png

 

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:

vrobertqmsft_0-1625042593505.png

 

 


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.

v-robertq-msft
Community Support
Community Support

Hi, @PaulusD 

According to your description and sample pictures, I can’ figure out what’s the logic of your calculated column [WorkDaysCounter], why it has navigated numbers? Would you like to explain it in detail?

 

What’s more, I think it’s better if you can upload the test pbix file without sensitive data or some sample data so that we can help you in advance.

 

How to Get Your Question Answered Quickly 

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.

Hello @v-robertq-msft ,

 

The idea is that we're measuring the count of workdays between two dates. Based on this we can measure if a shipment was delivered within contractual leadtime.

 

I'm fairly confident that the approach that i've taken is in line with best practices for this (based on info found in the community and the web).

If I can get this working, we could incorporate the national holidays and end up with a true nett measurement in terms of performance.

 

I treid two approaches.

One where a return a value of 1 (for workdays) and a 0 for weekends. In the calculate I count the total results betweendates.

The second is I check if the date is a workingday, returning true for workdays and false for weekends. In the calculate I used countrows filtering on true values.

 

neither of these provide the results I see others generating and I just cant figure out where i'm going wrong 😞

PaulusD
Helper I
Helper I

Any other tips I could try are welcome, I still haven't been able to solve this.
As mentioned, i'm working with a seperate date table.

 

Thanks.

parry2k
Super User III
Super User III

@lbendlin as @PaulusD  mentioned, he already have a calendar dimension and adding this weekday in that dimension. I think he is on the right track.

 

@PaulusD Provide more details in what case it is not returning the data. I see you are filtering calendar dimensions from the SO_DEL table which will give only those dates from the Calendar table that exist in the SO_DEL table, assuming these two tables have a relationship. Any reason why you are doing that?

 

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Hi @parry2k ,

 

From what i've read, it should be counting the rows in the datetable (dimension table), where the start date and end date for the countrows action is determined by the dates passed on from the fact table.

 

For some dates this works fine:

PaulusD_0-1624261488628.png

 

but for other dates is just completely misses the apex:

PaulusD_1-1624261684068.png

now the date entered in the source is incorrect, but the point is the results sould reflect the number of days over the years between the dates.

 

 

The datetable is built using the calendarauto function, so i'm pretty sure all dates from the earliest found up to the latest found are in that table.

 

I need to be able to rely on the forumula as i'm measuring performance based on dates.

lbendlin
Super User III
Super User III

Don't do that (trying to use the weekday function). Use a proper external calendar table that you create and maintain manually and that has the actual workdays for your scenario, excluding holidays etc.

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors