Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello Power BI experts!
I am looking for help to add a column that calculates the # of hours or days (not whole) between two date/times. I have included below a sample of the date/times I have and also have work days marked with a 1 and weekends and holidays with a 0 in my calendar. Either hours or days would work as I can calcualte hours back to days but whole days will not work.
I'm sorry if this has been answered before as I did search and tried several ways but have nothing seemed to work or fit for me and I am getting myself confused 😞 . Any help on how to do this would be greatly appreciated.
Thank you!
Samples of my dates:
Date/Time Opened | Date/Time First Resolved |
9/26/2018 11:44 | 9/26/2018 14:47 |
9/9/2018 14:37 | 9/18/2018 8:55 |
8/8/2018 9:06 | 9/7/2018 10:24 |
9/10/2018 14:41 | 9/10/2018 15:11 |
9/9/2018 14:37 | 9/18/2018 8:55 |
9/26/2018 8:28 | 9/26/2018 9:22 |
Solved! Go to Solution.
@Anonymous,
You can create a calculated column using DAX below.
Total Working Days Column = SUMX ( FILTER ( 'Calendar', 'Calendar'[Date] >= Table[Date/Time Opened] && 'Calendar'[Date] <= Table[Date/Time First Resolved] ), 'Calendar'[isWorkDay] )
However, if you need to calculate work hours between two date time valyues, please check the Power Query function in this similar thread: https://social.technet.microsoft.com/Forums/en-US/f3fc729e-a2ec-409a-b20e-b5cf8ff5dc2a/calculate-work-hours-between-two-timedate-stamps?forum=powerquery.
Regards,
Lydia
@Anonymous,
You can create a calculated column using DAX below.
Total Working Days Column = SUMX ( FILTER ( 'Calendar', 'Calendar'[Date] >= Table[Date/Time Opened] && 'Calendar'[Date] <= Table[Date/Time First Resolved] ), 'Calendar'[isWorkDay] )
However, if you need to calculate work hours between two date time valyues, please check the Power Query function in this similar thread: https://social.technet.microsoft.com/Forums/en-US/f3fc729e-a2ec-409a-b20e-b5cf8ff5dc2a/calculate-work-hours-between-two-timedate-stamps?forum=powerquery.
Regards,
Lydia
@v-yuezhe-msft did you created any calendar table,if yes - can you please share the DAX
hi,
I am working on the same thing. I have taken workdays as 1 and weekend and Holidays as 0, used mentioned DAX, but I am getting right for weekends and holidays as right value, but for weekdays +1 is added to the value which is not accpetable.
Ex. Weekday dates
Start date 14-11-2019
End date 16-11-2019
Difference should be 2 days but it is taking as 3 days, because it is summing from workday which is marked as 1 for weekdays. I am not sure where I am making an error.
Please let me know how to overcome this.
Plz share DAX code I am also facing same issue . please help
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
84 | |
83 | |
64 | |
60 | |
55 |
User | Count |
---|---|
179 | |
108 | |
105 | |
71 | |
70 |