Running MAXIMUM of measure result in a period.

[JenAbe]

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!

[JenAbe]

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!

[Jihwan_Kim · Accepted solution]

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] ) )
    )
)

[JenAbe]

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) 

[Jihwan_Kim]

Hi,

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

Thank you.