[Matrix] Date Hierarchy on column combined with measure

[marcusvmc]

Hi guys!

I need to accomplish a matrix something like that:

My test dataset is:

Id	SubClass_Id	StartDateTime	EndDateTime
1	1	13/2/19 7:00 AM	13/2/19 10:00 AM
2	2	26/2/19 12:00 PM	13/2/19 3:00 PM
3	20	18/2/19 6:00 PM	20/2/19 6:00 PM

I need to count how many hours are allocated in each day and then display this value in the correct position.

My question is:

How to write the right measure to calculate and display these values?

So far I have accomplished this:

Where the measure is being displayed everywhere and all subclasses appear in every single class.

PS: The class and subclass are properly linked in the Relationship tab.

I would really appreciate any suggestions.

Thanks!

[v-yulgu-msft · Accepted solution]

Hi @marcusvmc ,

New a blank query in Query Editor mode, paste below M code into Advanced Editor to generate a calendar table.

let
    
    StartDate= #datetime(2019,1,1,0,0,0),
    EndDate = DateTime.LocalNow(),  
    Source = List.DateTimes(StartDate, Number.From(EndDate)- Number.From(StartDate)+1 ,#duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each List.DateTimes([Column1], 24, #duration(0, 1, 0, 0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Custom", "Datetime"}})
    
in
    #"Renamed Columns"

Apply above changes. In Data view mode, add below calculated columns in above calendar table.

Year-Month = FORMAT([Datetime],"MMMM YYYY")
WeekNum = "Week-"&WEEKNUM([Datetime],2)
WeekDay = WEEKDAY([Datetime],2)

Create a calculated table.

crossjoin =
ADDCOLUMNS (
    FILTER (
        CROSSJOIN ( Query1, 'Source Table' ),
        [Datetime] >= 'Source Table'[StartDateTime]
            && [Datetime] < 'Source Table'[EndDateTime]
    ),
    "Hour", 1
)

Drag corresponding fields from 'CrossJoin' table into Matrix, as shown in below screenshot.

Best regards,

Yuliana Gu