Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
)
)
)
Solved! Go to 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
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
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |