Floating cumulative sum

[LuciaZel]

Hi everyone, 

I am new in Power BI and DAX, so I would like to ask a question. 

I have this table "Krist":

Rok = Year from dat_prov column; Mesiac = Month from dat_prov column, prov - súčet = sum of prov column

I need to calculate floating cumulative sum of "prov", which means the summary of all amount in date period 12 previous months. 

I created DAX statement:

dat_prov = date of prov

cumulative_prov_12M =

CALCULATE(

    SUM(Krist[prov]),

    ALL(Krist),

    DATESBETWEEN(

        Krist[dat_prov].[Date],

    DATEADD(LASTDATE(Krist[dat_prov].[Date]),-12,MONTH),

    LASTDATE(Krist[dat_prov].[Date])))

And I got the result right

When I transform table into line graph and I want to select in graph just period of date I can not do that. As you can see below on the second screen the "prov-súčet" is filtered by period of time (1.3.2022 - 28.2.2023), but the cumulative line is not. 

Is there anything wrong with the DAX statement or how can I solve it? 

Thanks for answers.

[andhiii079845 · Accepted solution]

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).

[LuciaZel]

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? 

[andhiii079845]

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.

[andhiii079845]

Can you please give the complete DAX statement of:

I created DAX statement:
dat_prov = date of prov

[LuciaZel]

Hi, 

sorry I used the wrong interpretation. Column "dat_prov" is regular column from imported table "Krist":

In power query I just changed the type to date and then transformed all the date into start of the month