Stop a Running Total at maximum value while showing less than and equal to values

[rusgesig]

I've been struggling with this particular item since day one, and every time I think I've made progress I hit the same wall.

Basically, I'm trying to show uptake over time, instead of using a date table, I have a series from 0-100 which represents months after activation.

When using the measure below:

Measure =

CALCULATE(

    SUM('Uptake'[Uptake]),

    FILTER(

        ALLSELECTED('MonthSeries'[MonthSeries]),

        ISONORAFTER('MonthSeries'[MonthSeries], MAX(Uptake[Months]), DESC)

    )

)

I get the following problem: For months in which there was no uptake, I want it to show the maximum up to that point, instead it shows a blank if nothing fell within that particular month.

However when I use this measure:

Measure =

CALCULATE(

    SUM('Uptake'[Uptake]),

    FILTER(

        ALLSELECTED('MonthSeries'[MonthSeries]),

        ISONORAFTER('MonthSeries'[MonthSeries], MAX(MonthSeries'[MonthSeries]), DESC)

    )

)

I get the problem of the values not stopping at their maximum, they just continue at the maximum value for the entire series, but I would like them to show blanks after the maximum value.

Is there anyway to get the best of both worlds? Show the value less than or equal to the current month, unless it's greater than the maximum, in which case leave it blank.

[amitchandak]

@rusgesig , Your second measure should stop

Measure =
CALCULATE(
SUM('Uptake'[Uptake]),
FILTER(
ALLSELECTED('MonthSeries'[MonthSeries]),
'MonthSeries'[MonthSeries]<= MAX('MonthSeries'[MonthSeries])
)
)

Or try Like

Measure =
var _maxx(ALLSELECTED('MonthSeries'),'MonthSeries'[MonthSeries])
return

CALCULATE(
SUM('Uptake'[Uptake]),
FILTER(
ALLSELECTED('MonthSeries'[MonthSeries]),
'MonthSeries'[MonthSeries]<= MAX('MonthSeries'[MonthSeries])
&& MonthSeries'[MonthSeries] <=_maxx
)
)

[rusgesig]

So is this a bug in Power BI?

[rusgesig]

Hi amitchandak, thanks for your reply.

Unfortunately your first measure returns the same results as my second measure (doesn't stop at month 25 in this case).

The second measure is not accepted due to a syntax error.

[rusgesig]

Bump

[rusgesig]

For some reason it keeps removing the screenshots I upload.

1st Measure:

2nd Measure :