How to get MAXX value for a Month when Date and Time are involved?

[blake_leblanc]

There is a FACT table that looks something like

Date	Hour	DateTime	Name	Number
1/19/2023	9	1/19/2023 09:00	Brad	50
1/19/2023	9	1/19/2023 09:00	Tim	35
etc etc

How can we get the largest sum of Numbers for each month, by Date and Hour?

I currently have DIM tables for Date (from start to end of data), Hour (1 to 24), and Name.

When I create a matrix visualization of the dataset, the measure currently seems to find the largest Number as it pertains to the daily totals rather than on the per hour level. 

Here is a mockup of the matrix visual and an sanitized/anonymized equivalent of the measure.

Thanks in advance for any pointers!

MaxPerMonth =
VAR IsPartOfCalculations =
    IF (
        [Number]
            <> BLANK (),
        1,
        0
    )
VAR MaxDate =
    CALCULATE (
        MAXX (
            TOPN (
                1,
                DIM_Dates,
                [Number], DESC
            ),
            DIM_Dates[Date]
        ),
        REMOVEFILTERS ( DIM_Names ),
        REMOVEFILTERS ( DIM_Dates[Day] ),
        REMOVEFILTERS ( DIM_Hours[Hour] )
    )
VAR MaxNumber =
    CALCULATE (
        MAXX (
            TOPN (
                1,
                DIM_Dates,
                [Number], DESC
            ),
            [Number]
        ),
        FILTER (
            DIM_Dates,
            DIM_Dates[Date] = MaxDate
        ),
        REMOVEFILTERS ( DIM_Dates[Day] ),
        REMOVEFILTERS ( DIM_Hours[Hour] )
    )
VAR Result =
    IF (
        IsPartOfCalculations,
        MaxNumber,
        BLANK ()
    )
RETURN
    Result

---
EDIT: Update formatting

[blake_leblanc · Accepted solution]

I made some progress by drastically simplifying the MAXX measure.

If the visual matrix is limited to the Year and Month, this gets the proper MAXX total

MaxNumber =

MAXX(

    VALUES( FACT_Table[DateTime] ),

    [Number]

)

So for every datetime, we may have something like...

• Date | Time | DateTime | Name | Number
• ...
• 1/16/2023 | 9:00 | 1/16/2023 9:00 | Brad | $5
• 1/16/2023 | 9:00 | 1/16/2023 9:00 | Tom | $14
• 1/16/2023 | 9:00 | 1/16/2023 9:00 | Kim | $8
• ...

For the month of January, let's say the highest sum of Numbers for any given date time is this one, @ $27

Now that I know what the highest total number is across January, how do I get the DateTime that's associated with that $27 value?

Ideally, I'd just "look to" the corresponding DateTime column within the same row of the FACT table. But since the value is likely a SUMX across multiple rows, the Number value I'm searching for doesn't actually exist in the FACT table.

I keep thinking that a more PBI/DAX approach may be to get the date from the related DIM_Dates table and the hour from the related DIM_Hours table and then concatenate the two together in some fashion. 

Any suggestions?

---
EDIT: I've since worked out the Date and Hour calculations

MaxDate = 
VAR Number =
    CALCULATE(
        MAXX(
            VALUES( FACT_Table[DateTime] ),
            [Number]
        )
    )
VAR Result =
    CALCULATE(
        MAX( DIM_Dates[Date] ),
        FILTER( 
            ALL( DIM_Dates ),
            [MaxNumber] = Number
        )
    )
RETURN
    Result     

Similar for the Hour.

Then, to combine them into a DateTime I used

VAR MaxDate =
    [MaxDate]
VAR MaxTime = 
    TIME([MaxHour], 0, 0)
VAR MaxDateTime =
    MaxDate + TIME( HOUR( MaxTime ), MINUTE( MaxTime ), SECOND( MaxTime ) )
VAR Result = 
    MaxDateTime
RETURN
    Result