cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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

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

1 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``````

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``````

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors