sequence of calculate "max" in aggregation of a "running sum"

[elke_hh]

Hello Forum,

I have following problem when trying to calculate a "running sum" over a measure which determines a maximum value out of two measures.

The issue is, that I cannot force the determination of the max-value before it comes to the calculation of the sum. The determination of max-Value is correct on its own but in the aggregation of running-sum it looks as if first sum is caculatet and then max-Value.

My query looks like this:

RunningSum_ofMAX =

VAR RunningSum =

        CALCULATE (

           max([MeasureA], [MeasureB]),

                FILTER (

                    ALL ( D_DATETABLE ),

                    D_DATETABLE[Calendar Day] <= MAX ( D_DATETABLE[Calendar Day] )

                )

            )  

RETURN

    RunningSum

MeasureA and MeasureB are simple calculations:

calculate(sum(Table[Quantity]),D_DATETABLE[ROLLING 12 M],Filter(ALL(D_MEASURE),D_MEASURE[MEASURE_ID] = 1)))

It would be great, to get a hint

Thanks Elke

[elke_hh · Accepted solution]

I will resolve my issue as follows:
The measure is used in datecontext "Year-Month" and "Year-Week".
For this I will use a tablefunction with max values of [MeasureA] and [MeasureB] for each "Year-Month" (same for "Year-Week) . This max-Value can be summed up with sumx iteratly in the running sum.
Not the generic solution I would prefer but no longer worth time to spend on

[Jihwan_Kim]

Hi,

I am not sure how your semantic model looks like, but please try something like below.

RunningSum_ofMAX =
VAR RunningSum =
    SUMX (
        FILTER (
            ALL ( D_DATETABLE ),
            D_DATETABLE[Calendar Day] <= MAX ( D_DATETABLE[Calendar Day] )
        ),
        CALCULATE ( MAX ( [MeasureA], [MeasureB] ) )
    )
RETURN
    RunningSum

[elke_hh]

Thank you Jihwan_Kim.

I tried your query, but the result is running sum of (MeasureA+MeasureB).