Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |