Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

DATEDIFF working days between 2 dates

Hey all,

 

I've followed the "DATEDIFF Working Days" instructions for calculate the working days. In fact, I have a table with all the days and a "1" in the saturdays, sundays and holidays ("0" on work days). But I'm not getting what I want. Could you help me?

 

Also, I have a table like this:

IDStart DateFinal DateDATEDIFFDATEDIFF1
12301/01/201802/01/20181?
12405/01/201810/01/20185?
12515/01/201801/02/201817?
12618/01/201820/01/20182?

 

I got a DATEDIFF between dates but I want the DATEDIFF discounts the saturdays, sundays and holidays. I thoght about calculate how many saturdays, sundays and holidays are between start and final date and then rest it in DATEDIFF column. But I don't know how I should say it to PowerBI.

 

Regards

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

See if this helps:

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

See if this helps:

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Yes! It worked for me.

 

In your example you only have 1 holiday (25/12/2017). I'm trying to add more dates; 26/12/2017, etc.

 

Just a quick question, in order to add more holidays, you need to add them in both places (table and formula). Is it right? I thought it should be added only in the table and then the formula gets all these holidays.

 

Regards

No, you should just have to add them in one place. I used a DATATABLE in the measure, just so that everything was contained in the measure itself. In practice, I would expect that you would have a separate table of holidays and you would just load that into the holidays var like this:

 

NetWorkDaysHolidays = 
VAR Calendar1 = CALENDAR(MAX(NetWorkDays[created date]),MAX(NetWorkDays[review date]))
VAR Holidays1 = 'HolidayTable'
VAR Calendar2 = EXCEPT(Calendar1,Holidays1)
VAR Calendar3 = ADDCOLUMNS(Calendar2,"WeekDay",WEEKDAY([Date],2))
RETURN COUNTX(FILTER(Calendar3,[WeekDay]<6),[Date])

In this case, you would have a table called "HolidayTable".


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Hi again Greg,

 

I followed your instructions but Powerbi doesn't find the table. It seems I have to write a measure in "VAR Holidays1 =" but the table with all the dates are not allowed:Captura.JPG

 

Regards

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.