Forum Discussion

michalb1's avatar
michalb1
Regular Visitor
3 years ago

Calculating hour in range timestmap divded by given month

Hello,

 

Sample table:

 

I have calculated the hour between two timestamps.

Duration_Hours = DATEDIFF('Work'[Work_Start],'Work'[Work_End],HOUR)

but, I would also like to know how many hours are in a given month if the timestamps are between 2 months period like object_3.

 

Now object_3 duration is only in March.

Is there any option to divide that duration hours into appropriate months?

 

Sample .pbix file:

https://we.tl/t-k9B1wehb8g

 

Best Regards,

Michal

1 Reply

  • PurpleGate's avatar
    PurpleGate
    Icon for Resolver III rankResolver III
    3 years ago

    Hi, 

    Please refer to this post,full credit to AllisonKennedy. https://community.fabric.microsoft.com/t5/Desktop/How-to-calculate-hours-between-to-DateTimes-amp-show-hours-per/td-p/2138245

     

    The measure needs a bit more work, but at least to start you off? 

     

     

     

    1. Create a date table. If you haven't got one, here is one using power query. 

     

     

    let
// Edit this start date to match your dataset.
    // 
    // Basic Date table courtesy of www.excelwithallison.com
    startDate = #date(2019, 1, 1),
endDate = Date.EndOfYear(Date.From(DateTime.LocalNow())),
Dates = List.Dates(startDate, Duration.Days(endDate - startDate), #duration (1,0,0,0)),
#"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    #"Inserted DateKey" = Table.AddColumn(#"Changed Type", "DateKey", each Date.ToText([Date],"yyyyMMdd"), type text),
    #"Inserted Year" = Table.AddColumn(#"Inserted DateKey", "Year", each Date.Year([Date]), Int64.Type),
    #"Inserted Quarter" = Table.AddColumn(#"Inserted Year", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
#"Inserted Month Name" = Table.AddColumn(#"Inserted Quarter", "Month name", each Date.MonthName([Date]), type text),
#"Inserted Month" = Table.AddColumn(#"Inserted Month Name", "Month number", each Date.Month([Date]), Int64.Type),
    #"Inserted Day of Month" = Table.AddColumn(#"Inserted Month", "Day of month", each Date.Day([Date]), Int64.Type),
    #"Inserted Day of Year" = Table.AddColumn(#"Inserted Day of Month", "Day of Year", each Date.DayOfYear([Date]), Int64.Type),
    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Day of Year", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Week", "Day name", each Date.DayOfWeekName([Date]), type text),
    // In Week functions
    // 0 represents Sunday start
    // 1 represents Monday start
    // 2 represents Tuesday start
    #"Inserted Week of Year" = Table.AddColumn(#"Inserted Day Name", "Week of Year", each Date.WeekOfYear([Date],1), Int64.Type),
#"Inserted Week of Month" = Table.AddColumn(#"Inserted Week of Year", "Week of Month", each Date.WeekOfMonth([Date],1), Int64.Type),
    #"Inserted Suffix" = Table.AddColumn(#"Inserted Week of Month", "Start of Day", each Text.From([Date], "en-NZ") & " 12:00 am", type text),
    #"Inserted Suffix1" = Table.AddColumn(#"Inserted Suffix", "End of Day", each Text.From([Date], "en-NZ") & " 11:59 pm", type text),
    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Suffix1",{{"Start of Day", type datetime}, {"End of Day", type datetime}})
in
    #"Changed Type1"

     

     

     

    2. Measure in your Work Table:

     

    Working Time = 

VAR _Result = 
    SUMX('Work', 
        
        VAR _WorkStart = 'Work'[Work_Start]
        VAR _WorkEnd = 'Work'[Work_End]
        VAR _DimDate = FILTER('Date', 
            ('Date'[Start of Day] <= _WorkStart && _WorkStart <= 'Date'[End of Day] )
            ||( 'Date'[Start of Day] <=_WorkEnd && _WorkEnd <= 'Date'[End of Day] )
        )
        VAR _DayStart = MINX(_DimDate,  'Date'[Start of Day])
        VAR _DayEnd = MAXX(_DimDate, 'Date'[End of Day] )
        VAR _Start = MAX(_WorkStart, _DayStart)
        VAR _End = MIN(_WorkEnd, _DayEnd)
        RETURN
        DATEDIFF(_Start, _End, MINUTE)/60
    )
RETURN
_Result