cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
blake_leblanc
Advocate I
Advocate I

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

There is a FACT table that looks something like

DateHourDateTimeNameNumber
1/19/202391/19/2023 09:00Brad50
1/19/202391/19/2023 09:00Tim35
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

 

 

blake_leblanc_0-1676390083981.png

---
EDIT: Update formatting

1 ACCEPTED SOLUTION
blake_leblanc
Advocate I
Advocate I

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

 

 

View solution in original post

1 REPLY 1
blake_leblanc
Advocate I
Advocate I

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

 

 

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors