Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Time difference per day

Hi Everybody, I have sample data as below. Looking to find time in mins per day & trying to show them in calender visual similar as below. How can i calculate time per day? Please help.   Event...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Everybody,

    Solved this using similar logic amitchandak .

     

    Created a time calender table  "Date" using below M language code:

     

    let
    Start = Date.AddMonths(Date.StartOfMonth(DateTime.LocalNow()),-11),
    End = Date.AddMonths(Start,12),
    Count = Number.From(End-Start)*1440,
    Calendar = Table.FromColumns({List.DateTimes(Start,Count,#duration(0,0,1,0))}, type table[DateTime=datetime]),
    #"Renamed Columns" = Table.RenameColumns(Calendar,{{"DateTime", "Date"}})
    in
    #"Renamed Columns"

     

     

    and created a column "Count" with default value 1.

     

    and Created a measure "Value"

     

    Value = CALCULATE(SUMX(FILTER(CROSSJOIN('Event Log','Date'),'Date'[Date] >= 'Event Log'[Start Date] && 'Date'[Date]< 'Event Log'[End Date]),'Date'[Count]))
     
    Which I used to display on the calender.
     
    Thanks