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
burnt_fingers
Advocate I
Advocate I

Calculating Business Days between 2 dates.

Hi All,

   So i have a calendar table that looks like this :

burnt_fingers_0-1595258163914.png

 

Then in other table i have 2 dates between i need to calculate business day diff.

I'm using following formula :

burnt_fingers_1-1595258218502.png

 

Yet, my __Days_Diff column only get 1's and few occasional 0's, which is obviously wrong

burnt_fingers_2-1595258287194.png

 

Can any1 advise what I'm doing wrong here ?

 

Best regards.

4 REPLIES 4
Greg_Deckler
Super User
Super User

@burnt_fingers - See if this helps: https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/td-p/367362

 

Also, I see you are a new member, Please check out this article: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.


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

@Greg_Deckler Thx for lots of usefull materials.

 Unfortunatly your solution does not work either, returns just '1' on each row.

 

So my sample data looks like this 

Custom_Creation	Custom_Corretion
4/16/2020	4/16/2020
4/14/2020	4/14/2020
7/9/2020	7/9/2020
7/8/2020	7/9/2020
7/1/2020	7/2/2020

 

I would like to get difference in working days calculated between those two.

As you can read above, that formula is almost direct copy paste from this forum. 

What other requirements are there to make it work ?

I do have active relationship between my __Calendar table and my RAW_Errors Table.

 

Below is my __Calendar Table.

 

Date	Year	Month	Day of the Week	Working Day	Date_Formatted	Date_Formatted_2
1/1/2020 12:00:00 AM	2020	1	4	1	01/01/2020	Wednesday, January 1, 2020
1/2/2020 12:00:00 AM	2020	1	5	1	01/02/2020	Thursday, January 2, 2020
1/3/2020 12:00:00 AM	2020	1	6	1	01/03/2020	Friday, January 3, 2020
1/4/2020 12:00:00 AM	2020	1	7	0	01/04/2020	Saturday, January 4, 2020
1/5/2020 12:00:00 AM	2020	1	1	0	01/05/2020	Sunday, January 5, 2020
1/6/2020 12:00:00 AM	2020	1	2	1	01/06/2020	Monday, January 6, 2020
1/7/2020 12:00:00 AM	2020	1	3	1	01/07/2020	Tuesday, January 7, 2020
1/8/2020 12:00:00 AM	2020	1	4	1	01/08/2020	Wednesday, January 8, 2020
1/9/2020 12:00:00 AM	2020	1	5	1	01/09/2020	Thursday, January 9, 2020
1/10/2020 12:00:00 AM	2020	1	6	1	01/10/2020	Friday, January 10, 2020
1/11/2020 12:00:00 AM	2020	1	7	0	01/11/2020	Saturday, January 11, 2020
1/12/2020 12:00:00 AM	2020	1	1	0	01/12/2020	Sunday, January 12, 2020

 

Hi  @burnt_fingers ,

 

Create a measure as below:

Measure = CALCULATE(SUM('Calendar table'[Working day]),FILTER('Calendar table','Calendar table'[Date]>MAX('Error'[Custom_Creation])&&'Calendar table'[Date]<=MAX('Error'[Custom_Corretion])))+0

And you will see:

Annotation 2020-07-21 135732.png

If you wanna the same dates return 1 instead of 0,then modify the measure as below:

Measure = CALCULATE(SUM('Calendar table'[Working day]),FILTER('Calendar table','Calendar table'[Date]>=MAX('Error'[Custom_Creation])&&'Calendar table'[Date]<=MAX('Error'[Custom_Corretion])))+0

For details,pls see attached.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

Hi,

  Thanks for your inputs but i was able to get my results slightly reworking @Greg_Deckler foruma.

It looks like this now :

DaysDiff = 
Var Calendar1 = CALENDAR(DURATION_ERRORS[Creation],IF(ISBLANK(DURATION_ERRORS[Correction]),DURATION_ERRORS[Creation],DURATION_ERRORS[Correction]))
VAR Calendar2 = ADDCOLUMNS(
                    Calendar1,"wkd",WEEKDAY([Date],2))
RETURN COUNTX(FILTER(Calendar2,[wkd]<6),[Date])-1

 

yet im still wondering why the original formula did not work for me.

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.