Filter workingdays and holidays in a DAX formula between two dates

[PimE]

Hello everyone,

I'm currently working on a dashboard for a company in the dark fiber industry.

The goal is to manage and improve the lead time of the projects by providing the project leaders a Power BI dashboard.

A project has ~15 steps from start to end. Once a step is completed, the project leader fills in the date in the project administration (Microsoft Dynamics 365). This is the data I use in Power BI.

I'm currently using DAX to calculate the difference between:

1. Date from step X and the current date; or
2. Date from step X and stap Y

Example 1

Days Elapsed no RfU sent =

DATEDIFF(

SELECTEDVALUE('Import CRM'[Technically ready] ),

TODAY(),

DAY

)

Example 2

Days Elapsed lead time=

DATEDIFF(

SELECTEDVALUE('Import CRM'[Project Ready] ),

SELECTEDVALUE('Import CRM'[start project] ),

DAY

)

What I want to achieve

Is it possible to add a filter to the DAX formula to only calculate working days and exclude holidays? I already have 'The Extended Date Table' from Enterprise DNA and a holiday table added to my PowerBI file. I thought I could use this as a filter: 'is workingday YES' and  'is holiday NO'

Or is my current formula only and ALWAYS going to calculate the difference in days between date X and Y?

If you need more information, please let me know.

[v-xicai]

Hi   @PimE ,

Do the suggestions from engineers make sense? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy

[v-xicai]

Hi @PimE ,

You can create calendar table first,  then create relationships among the calendar table , Data table and Holiday table on date field, keep Cross filter direction as Both.

Table: 
DateDim = CALENDAR(MIN(Data[DATE]),MAX(Data[DATE]))

Secondly, create column in Data table like DAX below.

Column: 

IsWorkDay = IF (WEEKDAY(MAX(DateDim[Date]),2)<=5 && COUNTX(RELATEDTABLE(Holiday),1)<1,1,0)

To calculate the workdays exclude holidays, you may refer to my reply in this similar case, and download the pbix file from here.

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PimE]

Please accept my apologies for the delayed response.

Thank you for providing me with a different solution to my problem.

I tried it (with the help of the provided pbix file) but I had no succes. I'm afraid this is not the solution that I'm looking for.

Let me give you some more informating by providing a screenshot of the dashboard. 

https://snipboard.io/VjDa67.jpg 

(the dashboard has been anonymized)

A: the date of the order confirmation by the customer of project W1671

B: the date of 'bestek verzonden' of project W1671. This step hasen't been completed.

C: since B hasn't been filled in yet, I want Power BI to calculcate the difference in working days between date A and the current date of project W1671

[AllisonKennedy]

You could do a FILTER on your Date table, then SUMX the IsWorkingDay column if it's 0 and 1, or use an if statement if not.

WorkingDays=
VAR lastdate = MAX(DimDate[Date])
RETURN
SUMX(FILTER(ALL(DimDate), DimDate[Date] <= lastdate), DimDate[IsWorkingDay])

[PimE]

Thanks for your quick reply Allison. 

This is the second time I write this reply, for some reason the previous one didn't post on Monday....

---

@AllisonKennedy wrote:
You could do a FILTER on your Date table, then SUMX the IsWorkingDay column if it's 0 and 1, or use an if statement if not.

---

I added the IsWorkingDay filter to the table and filtered on 'True'. This didn't change the amount of days. 

---

@AllisonKennedy wrote:
WorkingDays=
VAR lastdate = MAX(DimDate[Date])
RETURN
SUMX(FILTER(ALL(DimDate), DimDate[Date] <= lastdate), DimDate[IsWorkingDay])

---

I'm afraid I can't quite follow your suggestion. Is this formula part of your first solution? Or is it a new way of calculating the difference between date X and Y? And do I have to change the formula (so it works with my data)?

To be honest with you, I was hoping for a solution that only added a filter to my DAX formula. Hope this helps you 🙂