Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code FABINSIDER for a $400 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
I've got two date columns (created and modified), and I can calculate the hours between them simply using subratction. The issue is that I don't want to include weekend hours along with hours from 5pm to 7am.
So if the two dates are 10/20/2017 11am and 10/23/2017 8am I would like it to return 7. Now it returns 69.
Any help would be appreciated, Thanks in advance!
Solved! Go to Solution.
Hi @jshinnenkamp,
Open Query Editor mode, refer to below steps.
1. Add two custom columns to get the date part from [Created] and [Modified].
CreatedDate=DateTime.Date([Created]) ModifiedDate=DateTime.Date([Modified])
2. Change the data type of new columns from Date to Whole Number.
3. Add a new custom column [DateRange] and expand it. After expanding, remember to change data type of [Create date], [Modified Date] and [Date Range] to Date.
DateRange={[Created date]..[Modified Date]}
4. Create a custom column to get the weekday using below code. And filter this new column with below condition.
WeekDay=Date.DayOfWeek([Date Range])
5. Create two custom columns.
StartTime= if [Date Range]=[Created date] then Time.Hour([Createed]) else 7
EndTime= if [Date Range]=[Modified Date] then Time.Hour([Modified]) else 17
6. Similar to above step3, create a list column and expand it.
TimeRange={[StartTime]..[EndTime]-1}
7. Save and close all above changes. Then, in data view mode, create a new calculated table.
Result Table = SUMMARIZE ( 'Hour Table', 'Hour Table'[Createed], 'Hour Table'[Modified], "Hour Interval", COUNT ( 'Hour Table'[TimeRange] ) )
Best regards,
Yuliana Gu
Hi @jshinnenkamp,
Open Query Editor mode, refer to below steps.
1. Add two custom columns to get the date part from [Created] and [Modified].
CreatedDate=DateTime.Date([Created]) ModifiedDate=DateTime.Date([Modified])
2. Change the data type of new columns from Date to Whole Number.
3. Add a new custom column [DateRange] and expand it. After expanding, remember to change data type of [Create date], [Modified Date] and [Date Range] to Date.
DateRange={[Created date]..[Modified Date]}
4. Create a custom column to get the weekday using below code. And filter this new column with below condition.
WeekDay=Date.DayOfWeek([Date Range])
5. Create two custom columns.
StartTime= if [Date Range]=[Created date] then Time.Hour([Createed]) else 7
EndTime= if [Date Range]=[Modified Date] then Time.Hour([Modified]) else 17
6. Similar to above step3, create a list column and expand it.
TimeRange={[StartTime]..[EndTime]-1}
7. Save and close all above changes. Then, in data view mode, create a new calculated table.
Result Table = SUMMARIZE ( 'Hour Table', 'Hour Table'[Createed], 'Hour Table'[Modified], "Hour Interval", COUNT ( 'Hour Table'[TimeRange] ) )
Best regards,
Yuliana Gu
Hello @v-yulgu-msft ,
In my particular case I have data related to Service Ticket Creation (created) and Service Arrival Date (modifed). I may have multiple Service Arrival Dates as follows:
1. Technician scheduled but has not arrived, this will give me a date of 30-12-1899
2. Technician arrived, this will give me an arrival date other than 30-12-1899
3. Technician rescheduled for a future date, this will give me a date of 30-12-1899
How can I modify the Modified Date in your example to take the first/earliest date that is not 30-12-1899?
Is it posssible to do a calculation within the hour table instead of having another table - result table. I have other data in the hour table which I want to use but cannot connect the tables using a merge or append query as there is no suitable field. Kind regards
Hello,
I am performing the listed steps, but when I'm introducing 7, PBI takes it as 19:00. Any sugestion?
Thank you
StartTime= if [Date Range]=[Created date] then Time.Hour([Createed]) else 7
EndTime= if [Date Range]=[Modified Date] then Time.Hour([Modified]) else 17
What if "Created" field sometimes is an empty field?
i'm trying with your solution but there are some fields in my table that are empty in the first date (Created, in this case)
Im calculating working hours from two dates and i want to exclude non-working hours, weekends and holidays..
Thank you very much.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the February 2025 Power BI update to learn about new features.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
84 | |
69 | |
68 | |
39 | |
37 |