Calculating working days

[PaulusD]

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

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

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

[v-robertq-msft]

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:

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]

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()

2. Create a calculated column in the calendar table:

Isworkday =

IF(WEEKDAY([Date],2)>5,0,1)

3. Don’t give them a relationship:

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

[PaulusD]

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

[PaulusD]

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.

[PaulusD · 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:

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]

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:

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.

[Sapiec]

Hi @v-robertq-msft 

I was looking for something similar and your solution here works fine for the purposes of my issue.

The only thing, is I would like to add in this the Bank Holidays? 

Regards,

[PaulusD]

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:

---

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]

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.

[PaulusD]

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]

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]

@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.⚡

[PaulusD]

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:

but for other dates is just completely misses the apex:

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]

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.