Rolling monthly total

[Ragnarock1982]

Morning all,

I'm having an 'issue' with the rolling monthly total on my report.

Basically, I have a date slider and a Line & Stacked column chart. When I select dates from the slider the chart is updated BUT it is still showing the running total for the months that I haven't got selected.

EG, If I select JANUARY - APRIL in the slider, it shows ALL months up to december in the chart, but for MAY to DECEMBER it is just the same figure.

What I would like, is if I select JAN - APR in the slider then MAY - DEC is blank in the chart.

I have done this on another chart which shows monthly figures (not rolling) and it works fine. ( I have another calculated line in both charts which is calculated up to DEC, to ensure that the months still appear in the charts)

I think I'm missing something out of my DAX query, but not sure what. Any thoughts?

DAX is:

AA running total in Month =
CALCULATE(
 SUM('Balances - Balance Sheet'[AA]),
 FILTER(
  CALCULATETABLE(
   SUMMARIZE(
    'Calendar',
    'Calendar'[Date].[MonthNo],
    'Calendar'[Date].[Month]
   ),
   ALLSELECTED('Balances - Balance Sheet'))
  ,
  ISONORAFTER(
            'Calendar'[Date].[MonthNo], MAX('Calendar'[Date].[MonthNo]), DESC,
   'Calendar'[Date].[Month], MAX('Calendar'[Date].[Month]), DESC
        )
    )
)

[v-lili6-msft · Accepted solution]

hi, @Ragnarock1982 

You could use this measure as below:

New AA running total in Month = 
CALCULATE(
	SUM('Balances - Balance Sheet'[AA]),
	FILTER(
		ALLSELECTED('Calendar'),
		ISONORAFTER('Calendar'[MonthNo], MAX('Calendar'[MonthNo]), DESC)
	)
)

or 

AA running total in Date = 
CALCULATE(
	SUM('Balances - Balance Sheet'[AA]),
	FILTER(
		ALLSELECTED('Calendar'[Date]),
		ISONORAFTER('Calendar'[Date], MAX('Calendar'[Date]), DESC)
	)
)

Result:

Best Regards,

Lin

[Ragnarock1982]

@v-lili6-msft  . . . Perfect!

The first DAX worked exactly as needed.

Thank you.