Forum Discussion

Ragnarock1982's avatar
Ragnarock1982
Frequent Visitor
7 years ago
Solved

Rolling monthly total

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
        )
    )
)

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    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

     

     

2 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    7 years ago

    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