Forum Discussion
Floating cumulative sum
I build a example with your infos. I think the problem is your automatic time intelligence.
For example: If you use the automatic time intelligence filter: blue one the filtering is correct. If you use the regular date column it not work. The time intelligence is like a hidden dimension table for the date. (please correct me someone if its wrong).
starting point:blue filtering (date - year):
The same via date (red). You see the cummlative has no filter.
The reason is, that you use ALL() in your DAX expression and the measure line ignore your filter (via selection the regular date column!) from the fact table. If you use the "hidden" dimension table filter via other fields the complete table is filtered for both (measure and column).
I build a example with your infos. I think the problem is your automatic time intelligence.
For example: If you use the automatic time intelligence filter: blue one the filtering is correct. If you use the regular date column it not work. The time intelligence is like a hidden dimension table for the date. (please correct me someone if its wrong).
starting point:
blue filtering (date - year):
The same via date (red). You see the cummlative has no filter.
The reason is, that you use ALL() in your DAX expression and the measure line ignore your filter (via selection the regular date column!) from the fact table. If you use the "hidden" dimension table filter via other fields the complete table is filtered for both (measure and column).
Thank you very much it works, you are a hero π
Can I have one more question?
How can I select in graph just 12 previous months to show? The result should be, that I will connect our Database to Power BI and every month it should show new data for actual month and 11 previous months.
For example, today is the 3th of March 2023, so I want to see on graph data for 12 closed months, which means from march 2022 to february 2023 and it should float every month, so on the 3th of april 2023 it should show data in graph from april 2022 to march 2023, etc.
How can I select it in filter please?
Please, do not forget to flag my reply as a solution. Thank you π
I would give you the advice to create a regular dimensional table for the calendar/date and disable this auto time intelligence. Than you will have all possilities to get the result you want.