cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hluque Frequent Visitor
Frequent Visitor

Cumulative total not working

Hello,

 

I'm facing an issue with my cumulative total that is surely related to the applied filters. I need to calculate the cumulative revenue and expenses by month and by client.

 

I've used the following formula but it only works when showing the total, if I add a client filter it won't work, it will simply show the total revenue of each month for that client:

 

CALCULATE([IMS_Revenue];filter(ALL('Raw Data');'Raw Data'[Year/month]<=MAX('Raw Data'[Year/month]))

Alternatively, I've used this one with ALLSELECTED that works when I select a client from the visual level filters but not if I want to use a it on matrix:

 

CALCULATE([IMS_Revenue];filter(ALLSELECTED('Raw Data');'Raw Data'[Year/month]<=MAX('Raw Data'[Year/month]))

This is how the tables are related:

Running total error_2.JPG

 

 

Can you help me here?

 

Thanks a lot.

1 ACCEPTED SOLUTION

Accepted Solutions
hluque Frequent Visitor
Frequent Visitor

Re: Cumulative total not working

At the end I figured it out, I needed to use the calendar table. Thus it allows me to filter by any client:

 

IMS_Revenue acumulado = CALCULATE([IMS_Revenue];filter(ALL(Calendario);Calendario[Date]<=MAX(Calendario[Date])))

Regards.

2 REPLIES 2
jthomson New Contributor
New Contributor

Re: Cumulative total not working

You'll want to look into using ALLEXCEPT rather than ALL and indicate the field which contains your client names. ALL's going to ignore the filters you set on the clients unless you tell it otherwise

hluque Frequent Visitor
Frequent Visitor

Re: Cumulative total not working

At the end I figured it out, I needed to use the calendar table. Thus it allows me to filter by any client:

 

IMS_Revenue acumulado = CALCULATE([IMS_Revenue];filter(ALL(Calendario);Calendario[Date]<=MAX(Calendario[Date])))

Regards.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 116 members 1,681 guests
Please welcome our newest community members: