Filter rolling sum by status in current month

[Anonymous]

Hi all,

I have a table as shown below with many more materials, and a rolling sum measure for each material that usually works just fine.

MATERIAL	Date	STD_MARGIN	LEM_STATUS
A311719	31.03.2022	0	Yellow
A311719	28.02.2022	0	Yellow
A311719	31.01.2022	0	Yellow
A311719	31.12.2021	0	Yellow
A311719	30.11.2021	0	Yellow
A311719	31.10.2021	0	Red
A311719	30.09.2021	22,37	Red
A311719	31.08.2021	0	Yellow
A311719	31.07.2021	0	Yellow
A311719	30.06.2021	0	Yellow
A311719	31.05.2021	14,39	Yellow
A311719	30.04.2021	-0,33	Yellow

I'd like to filter by the status of the current month --> only data with e.g. status =yellow in current month is displayed, but the rolling sum should still take all past 12 months into account, no matter what status they had.

As of right now, I simply put the status in a slicer on my report page, but because the entire table is filtered by the status, the rolling sum value is incorrect.

For example for March and Status = Red --> 22,37 is displayed , however I'd want to have 22,37+14,39-0,33 =36,43. 

For March and Status =yellow 14,39-0,33=14,06 is calculated, but I'd also want 22,37+14,39-0,33 =36,43 as calculation.

So what I'm looking for is not exactly a filter for the entire table, but rather for the current month status , without messing up the rolling sum calculation.

Any help would be much appreciated!

[Anonymous]

Nevermind, I got it working, thanks for the help!! 

[Anonymous]

Hi @v-chenwuz-msft,

thanks for yor response, that measure seems to work, the Status is now excluded from the calculation.

The calculation is correct, but I would still need to filter by the status in the current month while still maintaining the correct sums over the last 12 months. So if i selected yellow, all last twelve months are calculated, independently of the status in each month, but only the current month=yellow Materials would show. As of right now, the status filter is excluded for the calculation, which is correct, but also for the filtering in the current month.
Would I need a seperate table with the status for this?

[v-chenwuz-msft · Accepted solution]

Hi @Anonymous ,

You need a separate date table to provide parameters for your formula.

And please try this formula to do it.

Measure =
VAR _end =
    MAX ( 'Date'[Date] )
VAR _start =
    DATE ( YEAR ( _end ) - 1, MONTH ( _end ), DAY ( _end ) )
RETURN
    CALCULATE (
        SUM ( 'Table'[STD_MARGIN] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[MATERIAL] ),
            'Table'[Date] >= _start
                && 'Table'[Date] <= _end
        )
    )

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.