topic Re: sequence of calculate "max" in aggregation of a "running sum" in DAX Commands and Tips

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

elke_hh — Wed, 05 Jun 2024 16:41:12 GMT

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

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

Jihwan_Kim — Wed, 05 Jun 2024 17:08:51 GMT

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

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

elke_hh — Thu, 06 Jun 2024 08:02:48 GMT

Thank you Jihwan_Kim.

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

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

elke_hh — Thu, 06 Jun 2024 11:08:02 GMT

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