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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ashutosh
Helper I
Helper I

Find the difference between dates excluding Weekends

Hi,

 

I have to calculate the difference between two dates but excluding the weekends (Holiday's may in in future) i.e, Sat & Sun  for now.

 

I checked many threads here all the solutions are with seperate Date table and I don't have any seperate date table 😞

 

Screenshot_4.png

1 ACCEPTED SOLUTION

@ashutosh,

 

I have tested it on my local environment, we can add a calculated column to check if the date is working, and then sum up this calculated column.

Weekday = WEEKDAY('Table'[Date])
IsWorkingday = IF('Table'[Weekday]>6||'Table'[Weekday]<2,0,1)

DateDifference =
var CurrentDate = CALCULATE(MAX(Table1[Date]))
var PreviousDate = CALCULATE(MAX(Table1[Date]),FILTER(ALL(Table1),Table1[Date]<MAX(Table1[Date])))
return
IF(ISBLANK(PreviousDate),BLANK(),
CALCULATE(SUM('Table'[IsWorkingday]),FILTER(ALL('Table'),'Table'[Date]>=PreviousDate && 'Table'[Date]<CurrentDate)))

Capture.PNG

 

Regards,

Charlie Liao

View solution in original post

6 REPLIES 6
Greg_Deckler
Super User
Super User

Not much to go on here, but could you create a calculated column and use WEEKDAY() to get the day of the week. Let's say Sunday is 1 and Saturday is 7.

 

Then you could create a measure like the following:

 

Measure = ([First Date] - [Second Date])*1. - CALCULATE(COUNT([Column]),FILTER(Table,([WeekDay] = 1 || [WeekDay = 7) && [Date] > [First Date] && [Date] < [Second Date]))

Something along those lines.



Follow on LinkedIn
@ 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!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler Thank you for your response.

 

For making it more simple I created a date table, and also created DayInweek as Saturday as 6 & Sunday as 7 and wrote something like you suggested:

 

I am creating a calculated column here.

 

Total Time = 1.0*(Operations_Data[LAST DELIVERED EVENT] - Operations_Data[FIRST IN TRANSIT EVENT]) - CALCULATE(COUNT('Calendar'[IfWorkDay]),FILTER('Calendar','Calendar'[IfWorkDay] = 6 || 'Calendar'[IfWorkDay] = 7 ))

But, I am not getting the proper result. Please suggest

@ashutosh,

 

I have tested it on my local environment, we can add a calculated column to check if the date is working, and then sum up this calculated column.

Weekday = WEEKDAY('Table'[Date])
IsWorkingday = IF('Table'[Weekday]>6||'Table'[Weekday]<2,0,1)

DateDifference =
var CurrentDate = CALCULATE(MAX(Table1[Date]))
var PreviousDate = CALCULATE(MAX(Table1[Date]),FILTER(ALL(Table1),Table1[Date]<MAX(Table1[Date])))
return
IF(ISBLANK(PreviousDate),BLANK(),
CALCULATE(SUM('Table'[IsWorkingday]),FILTER(ALL('Table'),'Table'[Date]>=PreviousDate && 'Table'[Date]<CurrentDate)))

Capture.PNG

 

Regards,

Charlie Liao

@v-caliao-msft 

 

Thank you so much.

 

That works great. But I have one more question to ask may be you could answer:

 

I have 4 columns in my table:

 

First column

Second Column

Third Column

Fourth Column

 

I calculte the date diffeerence between First and Fourth but sometimes, First column has null values so insted of first I have to choose Second Column value and same with Third and Fourth (If Fourth is null, I have to choose Third Column value).

 

How could I proceed in the same example as you suggested above.

@ashutosh,

 

You could use a IsBlank to check the column, such as

=IF(IsBlank(Table[Colum1]),Table[Column2],Table[Column1])

 

Regards,

Charlie Liao

You could create two new columns, Five and Six with formulas like:

 

 

Five = IF(ISBLANK([One]),[Two],[One])

Six = IF(ISBLANK([Four]),[Three],[Four])

Then just base everything off of columns Five and Six.

 



Follow on LinkedIn
@ 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!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

AugPowerBI_Carousel

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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