Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.