Showing results for 
Search instead for 
Did you mean: 

Hour Breakdown

Super User
Super User
Super User

Hour Breakdown

This Quick Measure breaks a start and end time down into the number of minutes for each hour of the day. There are actually two measures included in order to demonstrate how to use the techniques in Measure Totals, The Final Word to display the correct totals and subtotals within a matrix.



Hour Breakdown = 
VAR __currentHour = HOUR(MAX('Hours'[Hour]))
VAR __startHour = HOUR(MIN('Data'[Start]))
VAR __endHour = HOUR(MAX('Data'[End]))
VAR __table = GENERATESERIES(__startHour,__endHour,1)
VAR __table1 = ADDCOLUMNS(__table,"__minutes",
        __startHour < __endHour && [Value] <> __endHour && [Value] <> __startHour,60,
        __startHour < __endHour && [Value] = __endHour,MINUTE(MAX('Data'[End])),
VAR __table2 = FILTER(__table1,[__minutes]>0)
SUMX(FILTER(__table2,[Value] = __currentHour),[__minutes])




Hour Breakdown Total = 
VAR __table = SUMMARIZE('Data',[Date],[ID])
VAR __table1 = GENERATE(__table,Hours)
VAR __table2 = ADDCOLUMNS(__table1,"__duration",[Hour Breakdown])
IF(HASONEVALUE(Hours[Hour]) && HASONEVALUE(Data[ID]),[Hour Breakdown],SUMX(__table2,[__duration]))





I have book! Learn Power BI from Packt

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!