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

## Combining filters and cumulative sum

I have some filters that can be applied (like City, Department, id, and time period). For example, this code works:

`Cumulative_sum = CALCULATE( SUM(Table[ColumnA]) + SUM(Table[ColumnB]) + SUM(Table[ColumnC]); FILTER(ALL(Table(TIME_PERIOD); Table[TIME_PERIOD] <= MAX (Table[TIME_PERIOD])))`

When I tried to add the filter, my coding either returns an error or returns the cumulative sum starting on the time period filtered: For example, let's say I have a value of 10 in January, Feb, March, and April for the city A, department B. If I choose to show the values of March and April, it should show 30 and 40. However, the current graph shows the cumulative value of the filtered time period (and would show 10 and 20).

Here is what Im trying:

```   Cumulative_sum = CALCULATE( SUM(TableA[ColumnA]) + SUM(TableA[ColumnB]) + SUM(TableA[ColumnC]); FILTER(ALL(TableB(TIME_PERIOD); TableB[TIME_PERIOD] <= MAX (TableB[TIME_PERIOD])); FILTER(ALLSELECTED(TableA); TableA

[SITUATION] = "OK'))```

I have tried using the a column of time period inside TableA, and tried using TableB as a Time Period table independently. What is my mistake here?

5 REPLIES 5
Super User

Hi,

Share a dataset and show the expected result.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

How can I attach a file?

Edit: I uploaded the file at https://ufile.io/gdalp if that's ok

You can see in the graph, when showing the last 5 months (calendar), that the value for the feb 26 is 64. When I change the filter to show the last 4 months (calendar), it gives back 10 for feb 26, and thats the problem: When I add the filter to check if situation = "OK", the cumulative filter stop working as intended. ( It should show 64)

Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor
Super User

Hi,

Your question is not clear.  Convert the line graph into a simple Table.  Now tell e what exact numbers are you expecting against every date in the Time period column.

Regards,
Ashish Mathur
http://www.ashishmathur.com

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 - June 2024

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

#### Fabric Community Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors