cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rzavgazaryan
Helper IV
Helper IV

How to calculate hours between to DateTimes & show hours per day on x axis?

Hi all -

 

bit of a tricky situation here - I have a start time and end time column, and a lot of those transactions happen around midnight.

 

I need to show total hours per day, but because sometimes the end date time is the next day, the total hours per day is all messed up. Doesn't matter if I use start time or end time for X axis - numbers are still wrong.

 

What can I do to fix this? Is there a way for me to differentiate between the two days when that happens? Appreciate any input.

1 ACCEPTED SOLUTION

@rzavgazaryan  I first off don't recommend using DAX for your Date table, use Power Query : https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html

 

Next, don't relate the Date table to your job table if you want the 3 hours split over multiple days. Instead, you'll need a much more complicated DATEDIFF code using the Date table: 

https://excelwithallison.blogspot.com/2020/06/dax-approximate-lookup.html 

 

You'll need a start and end of day DateTime column in your Date table to make things easier, so I'd add 12:00am and 11:59pm to the Date column as two new columns in your date table: 

 

= Table.AddColumn(#"Inserted Week of Month", "Suffix", each Text.From([Date], "en-NZ") & " 12:00 am", DateTime.Type)

 

The Processing Time can be calculated with a DAX Measure: 

Processing Time =

VAR _Result =
SUMX(JobsDB,
 
VAR _JobStart = JobsDB[jobStart]
VAR _JobEnd = IF(ISBLANK(JobsDB[jobEnd]), JobsDB[queryTime], JobsDB[jobEnd])
VAR _DimDate = FILTER(DimDate,
(DimDate[Start of Day] <= _JobStart && _JobStart <= DimDate[End of Day] )
||( DimDate[Start of Day] <=_JobEnd && _JobEnd <= DimDate[End of Day] )
)
VAR _DayStart = MINX(_DimDate, DimDate[Start of Day])
VAR _DayEnd = MAXX(_DimDate, DimDate[End of Day] )
VAR _Start = MAX(_JobStart, _DayStart)
VAR _End = MIN(_JobEnd, _DayEnd)
RETURN
DATEDIFF(_Start, _End, MINUTE)/60
)
RETURN
_Result

 

Eventually you get this result (see attached file below signature): 

 

AllisonKennedy_0-1634356603460.png

 

 

 


Please @mention me in your reply if you want a response.

Check out my Tokyo 2020 report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

5 REPLIES 5
v-yalanwu-msft
Community Support
Community Support

Hi, @rzavgazaryan ;

If the interval involves two days, you can use if(), and then use the next day (0:00:00) as the end time, and the next day (0:00:00) as the end time  as the starting time, so you can create two measures, one to calculate the daily interval of startTime and one to calculate the daily interval of EndTime, and then summarize them as follows:

1.create a datediff base on starttime.

Processing Time (start) = var _maxstart=MAX([StartTime])
return  
IF (
    _maxstart = BLANK (),BLANK (),
    IF (
        MAX ( [EndTime] ) = BLANK (),
        DATEDIFF ( _maxstart , MAX ( [queryTime]), MINUTE ) / 60,
        IF(
            DATEDIFF(_maxstart,MAX([EndTime]),DAY)>=1,DATEDIFF(_maxstart,DATE(YEAR(_maxstart),MONTH(_maxstart),DAY(_maxstart)+1),MINUTE)/60,
        DATEDIFF (_maxstart, MAX ( [EndTime] ), MINUTE ) / 60
)))

2.create a datediff base on endtime.

Processing Time (end) =
VAR _end =
    MAX ( [EndTime] )
RETURN
    IF (
        MAX ( [StartTime] ) = BLANK (), BLANK (),
        IF ( _end = BLANK (),0,
            IF (
                DATEDIFF ( MAX ( [StartTime] ), _end, DAY ) >= 1,
                DATEDIFF ( DATE ( YEAR ( _end ), MONTH ( _end ), DAY ( _end ) ), _end, MINUTE ) / 60,0)))

The final output is shown below:

vyalanwumsft_0-1634612108831.png

3.create a table.

Table = SUMMARIZE('JobsDB',[ID],[StartTime], [EndTime],[queryTime],"Srart",FORMAT([StartTime],"yyyy-mm-dd"),"end",FORMAT([EndTime],"yyyy-mm-dd"),"process-s",[Processing Time (start)],"process-e",[Processing Time (end)])

4.create a sum measure.

total =
CALCULATE (
    SUM ( [process-s] ),
    FILTER ( ALL ( 'Table' ), [Srart] = MAX ( [end] ) )
)
    + CALCULATE ( SUM ( [process-e] ), ALLEXCEPT ( 'Table', 'Table'[end] ) )

The final output is shown below:

vyalanwumsft_1-1634612319141.png

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

mahoneypat
Super User
Super User

What @AllisonKennedy proposed is the way to do this with DAX.  It's not an easy calculation.  If you can transform your data further, you can simplify things.  For an example, create a blank query, open the advanced editor and replace the M code there with this.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI00Dc01TcyMDJUsLAyNFBw9EUVNDS0MgKLxuogNJghaQiAaTCHCJpYGcPVO6GZZYCiAWqKEdRaZA1QKXOo3RBBC4igMdSQ2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Line = _t, StartTime = _t, EndTime = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "StartTime", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"StartDate", "StartTime"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "EndTime", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"EndDate", "EndTime"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"StartDate", type date}, {"EndDate", type date}, {"StartTime", type time}, {"EndTime", type time}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each List.Dates([StartDate], Duration.TotalDays([EndDate]-[StartDate])+1, #duration(1,0,0,0))),
    #"Expanded DateRange" = Table.ExpandListColumn(#"Added Custom", "Date"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded DateRange",{{"Date", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "NewStartTime", each if [StartDate] = [Date] then [StartTime] else #time(0,0,0), type time),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "NewEndTime", each if [EndDate] = [Date] then [EndTime] else #time(23,59,59), type time),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom2",{"Line", "Date", "NewStartTime", "NewEndTime"})
in
    #"Removed Other Columns"

 

It shows how to convert a table like this

mahoneypat_0-1634381723705.png

 

into a table like this

mahoneypat_1-1634381775367.png

 

where there is a row for every date a line is in operation.  It works even if the operation spans 3+ days.  You can then relate the one date column to your Date table.  A model like this should simplify other calculations too (e.g., days in operation).  This is a fairly common use case, and a good topic for a video.  Thanks for the idea.

 

Also, please see this article for an easy way to calculate your total duration from a table like this (without adding a duration column subtracting the two time columns).

Calculate and Format Durations in DAX – Hoosier BI

 

Pat

 

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


rzavgazaryan
Helper IV
Helper IV

Hi all -

 

I'm currently vizualzing processing times per day for robots that are being ran. To find the processing time in HOURS, I created this calculated column:

 

Processing Time = IF(JobsDB[jobStart] = BLANK(),BLANK(),
IF(JobsDB[jobEnd] = BLANK(), DATEDIFF(JobsDB[jobStart],JobsDB[queryTime],MINUTE)/60,DATEDIFF(JobsDB[JobStart],JobsDB[jobEnd],MINUTE)/60))
 
So I have a start and end time for every job. If it's blank, it hasn't started yet (then blank), if JobEnd is blank, then it's still running, otherwise just get the date different in minutes/60 of start and end.
 
I then created a date table which is based on those start and end columns like so:
 
dimCalendar =

VAR BaseTable = CALENDAR(MIN(JobsDB[End Date]), MAX(JobsDB[End Date]))
Return
ADDCOLUMNS(
BaseTable,
"Year",YEAR([Date]),
"Month",FORMAT([Date], "MMMM"),
"Month Number", MONTH([Date]),
"Calendar Month",FORMAT([Date],"MMMM YY"),
"Month Year", FORMAT([Date],"YYYY MM"),
"Report Date",FORMAT([Date],"YYYY/MM/DD"))
 
 And I connected this date column to the end date column (for transaction metrics) - But now, I'm trying to accurately show how many hours processing per day. My issue is, there may be sometimes, when a process runs at 10 PM and end at 1 AM.. so that would mean that the visual would show that that Job/transaction ran for 3 hours on that next day. 
 
How do I solve this issue? I'd like to maybe modify the model or create a measure/calc column for processing time that ends at 11:59 before the next day so I can show these results correctly. If anyone has any idea as to how to go about this, I would really apperciate it.

Thank you!
 

 

 

@rzavgazaryan  I first off don't recommend using DAX for your Date table, use Power Query : https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html

 

Next, don't relate the Date table to your job table if you want the 3 hours split over multiple days. Instead, you'll need a much more complicated DATEDIFF code using the Date table: 

https://excelwithallison.blogspot.com/2020/06/dax-approximate-lookup.html 

 

You'll need a start and end of day DateTime column in your Date table to make things easier, so I'd add 12:00am and 11:59pm to the Date column as two new columns in your date table: 

 

= Table.AddColumn(#"Inserted Week of Month", "Suffix", each Text.From([Date], "en-NZ") & " 12:00 am", DateTime.Type)

 

The Processing Time can be calculated with a DAX Measure: 

Processing Time =

VAR _Result =
SUMX(JobsDB,
 
VAR _JobStart = JobsDB[jobStart]
VAR _JobEnd = IF(ISBLANK(JobsDB[jobEnd]), JobsDB[queryTime], JobsDB[jobEnd])
VAR _DimDate = FILTER(DimDate,
(DimDate[Start of Day] <= _JobStart && _JobStart <= DimDate[End of Day] )
||( DimDate[Start of Day] <=_JobEnd && _JobEnd <= DimDate[End of Day] )
)
VAR _DayStart = MINX(_DimDate, DimDate[Start of Day])
VAR _DayEnd = MAXX(_DimDate, DimDate[End of Day] )
VAR _Start = MAX(_JobStart, _DayStart)
VAR _End = MIN(_JobEnd, _DayEnd)
RETURN
DATEDIFF(_Start, _End, MINUTE)/60
)
RETURN
_Result

 

Eventually you get this result (see attached file below signature): 

 

AllisonKennedy_0-1634356603460.png

 

 

 


Please @mention me in your reply if you want a response.

Check out my Tokyo 2020 report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

AllisonKennedy
Super User
Super User

@rzavgazaryan  I believe I replied to you in a previous post - UPDATE : I have merged your previous post with this one and also see you've cross posted in the DAX forum too: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Processing-Utilization-Hours-per-Day-How-to-c... It's better to post only once so we can all see when it's answered and get inspiration from the other replies and also have all the info needed. I'll keep the DAX one posted, but will add a note that it's cross posted. When you resolve one, please be sure to mark ALL posts as solved. 

 

Thanks!

 

 

did you get my sample file? I think it does what you're asking for: 

 

AllisonKennedy_0-1634361326739.png

 

If not, please provide screenshots and indicate what's wrong/needs changed. 

 

If it does, and you can't replicate, again provide screenshots and details of what you don't understand/need more help with.

 

Cheers!

 

File is attached below signature. 

 


Please @mention me in your reply if you want a response.

Check out my Tokyo 2020 report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Top Solution Authors