topic Re: Running MAXIMUM of measure result in a period. in DAX Commands and Tips

Running MAXIMUM of measure result in a period.

JenAbe — Mon, 17 Jul 2023 21:32:13 GMT

I have a scenario where I need to determine the MAX value of a calculated measure within a period for a specific entity (ie by date and id).

I have been able to use DAX to get the MAX value by period by id overall BUT as the number fluxtuates during the time period being reported I want this measure to always reflect the maximum up until that point. To illustrate:

Note how the max always reflect the maximum value of all values between it and the start date (each id has a start date associated with it in this case April 21

The DAX I came up with which gives me an overall MAX reliably looks like this:

VAR a = CALCULATETABLE(
SUMMARIZE(
Calendar,
Calendar[Month-Year],
"# Items", [# Items Measure]
), NOT ISBLANK( OtherTable[Id] )
)

VAR b = CALCULATE( MAXX( a, [# Items] ),values(OtherTable[Id]) )

RETURN b

Any ideas? I am able to do this in SQL and in EXCEL. Can probably do it in M - but I really want this to be a calculated measure in DAX if possible. I mean, it should be possible?

Help!

Re: Running MAXIMUM of measure result in a period.

Jihwan_Kim — Tue, 18 Jul 2023 03:12:35 GMT

Hi,

I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

Max of value measure: = IF ( HASONEVALUE ( 'ID'[ID] ), MAXX ( WINDOW ( 1, ABS, 0, REL, SUMMARIZE ( ALL ( Data ), 'Calendar'[Month-Year sort], 'Calendar'[Month-Year], 'ID'[ID] ), ORDERBY ( 'Calendar'[Month-Year sort], ASC ), , PARTITIONBY ( 'ID'[ID] ) ), CALCULATE ( SUM ( Data[Count of Value] ) ) ) )

Re: Running MAXIMUM of measure result in a period.

JenAbe — Wed, 19 Jul 2023 13:38:26 GMT

Thank you so much, this solution worked with a little tweaking to the metric being MAXX'd - had some minor issues but otherwise good to go!

Re: Running MAXIMUM of measure result in a period.

JenAbe — Wed, 19 Jul 2023 20:42:09 GMT

Hey, the solution you gave me works but I have a small issue. My max measure seems to randomly return blanks for no apparent reason:

IF ( HASONEVALUE ( 'Table'[Id] ), MAXX( WINDOW( 1, ABS, 0, REL, SUMMARIZE( ALL( Table ), Calendar[Sort Month Year], Calendar[Month Year], 'Table'[Id] ), ORDERBY( Calendar[Sort Month Year], ASC ), , PARTITIONBY( 'Table'[Id] ) ), CALCULATE( [Measure Value Logic]) ) )

There is a value returned for the measure being 'maxed' but it doesn't want to display that value in some cases. I thought it may be HAS ONE VALUE -but it does have one value that can't be right...

Any ideas ( I realize this is a bit obscure)

Re: Running MAXIMUM of measure result in a period.

Jihwan_Kim — Thu, 20 Jul 2023 02:53:38 GMT

Hi,

Please share your sample pbix file's link, and then I can try to look into it.

Thank you.