Cumulative measure with criteria

[dixon1983]

Hi,

I'd like to create an area chart with weeks on the X Axis and Value on the Y Axis.

The Value needs to be cumulative, for example

MOQ Value	MOQ Weeks
100	6
200	7
400	10
200	16
100	52

Week 1 on the chart should show the total of MOQ Weeks > 1

Week 2 on the chart > 2

etc

I'm at a loss on how to do this and after reading numerous posts have given up.

I'd greatly appreciate if someone could help.

Thanks.

[v-chuncz-msft · Accepted solution]

@dixon1983,

You may refer to the measure below.

Measure =
VAR w =
    SELECTEDVALUE ( Table1[MOQ Weeks] )
RETURN
    SUMX ( FILTER ( ALL ( Table1 ), Table1[MOQ Weeks] > w ), Table1[MOQ Value] )

[dixon1983]

@v-chuncz-msft

Many thanks for the reply.

The measure works great apart from where the MOQ Weeks has zero count.

For example no part numbers have an MOQ weeks of 47. So instead of still summing MOQ Value for MOQ Weeks > 47 the measure would sum MOQ Value for MOQ Weeks > 0. This causes big spikes in the Area Chart.

Is there a way to Auto Filter these out? Or to amend the measure?

Thanks in advance.

[v-chuncz-msft]

@dixon1983,

You may simply add an IF Function.