cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Floating cumulative sum

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],
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?

1 ACCEPTED SOLUTION
Super User

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

Proud to be a Super User!

5 REPLIES 5
Super User

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

Proud to be a Super User!

Frequent Visitor

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?

Super User

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.

Proud to be a Super User!

Super User

Can you please give the complete DAX statement of:

`I created DAX statement:dat_prov = date of prov`

Proud to be a Super User!

Frequent Visitor

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors