topic Filter rolling sum by status in current month in DAX Commands and Tips

Filter rolling sum by status in current month

Anonymous — Wed, 01 Jun 2022 07:18:14 GMT

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!

Re: Filter rolling sum by status in current month

v-chenwuz-msft — Mon, 06 Jun 2022 02:26:14 GMT

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.

Re: Filter rolling sum by status in current month

Anonymous — Mon, 06 Jun 2022 07:11:13 GMT

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?

Re: Filter rolling sum by status in current month

Anonymous — Tue, 07 Jun 2022 09:11:56 GMT

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