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:
Can you help me here?
Thanks a lot.
Go to Solution.
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])))
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
Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.
We spoke with Power BI Super User, Greg Deckler, about his charity work
Continue your learning in our online communities.