The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 😞
Solved! Go to Solution.
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)))
Regards,
Charlie Liao
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.
@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
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)))
Regards,
Charlie Liao
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.
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
80 | |
78 | |
47 | |
39 |
User | Count |
---|---|
148 | |
115 | |
65 | |
64 | |
53 |