Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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 |
---|---|
77 | |
77 | |
68 | |
67 | |
49 |
User | Count |
---|---|
108 | |
104 | |
93 | |
83 | |
64 |