Forum Discussion
Running Total not working when selecting in Week filter/slicer
I have running total by week chart and legend by Year. I have few slicers and when I filter on those slicers they work fine except for filtering by Week which goes back to normal sum instead of running total.
Running total:
When filtering by Week slicer, the chart turns to normal sum:
Here are the formulas I tried:
Running total in Fiscal Week =
CALCULATE(
SUM('Sales'[Amount]),
FILTER(
ALLSELECTED('Sales'[Fiscal Week]),
ISONORAFTER('Sales'[Fiscal Week], MAX('Sales'[Fiscal Week]), DESC)
)
)
Running total in Week =
CALCULATE(
SUM('Sales'[Amount]),
FILTER(
ALLSELECTED('Sales'[Fiscal Week]),
('Sales'[Fiscal Week]<=MAX('Sales'[Fiscal Week]), DESC)
)
)
summer18 , Please move the filter to the date table. All date-related stuff.
Like
Running total in Week =
CALCULATE(
SUM('Sales'[Amount]),
FILTER(
ALLSELECTED('Date'[Fiscal Week]),
('Date'[Fiscal Week]<=MAX('Date'[Fiscal Week]), DESC)
)
)Also, use Year, month, a week from date table in visuals
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
3 Replies
- amitchandak
Super User
summer18 , Please move the filter to the date table. All date-related stuff.
Like
Running total in Week =
CALCULATE(
SUM('Sales'[Amount]),
FILTER(
ALLSELECTED('Date'[Fiscal Week]),
('Date'[Fiscal Week]<=MAX('Date'[Fiscal Week]), DESC)
)
)Also, use Year, month, a week from date table in visuals
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/- summer18
Helper III
Thanks amitchandak , it works!
- V-lianl-msft
Community Support
Hi summer18 ,
It may be that the value of the week slicer does not match the filter condition in measure.
Could you share the sample pbix via cloud service like onedrive for business or share the sample data?
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.