Don't want monthly balances to sum in pivot table because they are already cumulative

[Anonymous]

I have a pivot table with balances for each month.  The issue is that my balances are already cumulative, so when I drill up to the Year, quarter, it's summing the balances and I don't want them to sum. I only want to show the balances for each month and when I drill up to the full year or latest quarter, it should only show the most recent month's balances because as I mentioned they are already cumulative each  month.  Any help would be greatly appreciated!

[TomMartens]

Hey,

the challenge you are facing has a name (know your enemy 🙂 ), it's called semi-additive measure.

This article describes almost anything that will surface over time tha's time related: https://www.daxpatterns.com/time-patterns/

Watch for the semi-additve pattern.

Hopefully this provides the insights you are looking for.

Regards,

Tom

[Anonymous · Accepted solution]

@TomMartens Tom- Thanks so much for the prompt response and sharing the article! I'm sure it contains the solution I need, but I'm not skilled enough to know how to apply the DAX to my specific situation.  I simply have the month as the column headers with drill downs for Quarter and Year and I have the Balances in the rows.  So for example: My balances in March were 1,000,000, my balances in April were 2,000,000 and my balances in May were 3,000,000.  When I drill to the Quarter and Year, I want my balances to show 3,000,000, summarized for the most recent month only, but instead it's summing the balances across all of the months and returning 6,000,000.  Also, If I filter for a previous month, I would need it to summarize YTD, QTD, etc. for whatever month I've filtered on.  Are you able to provide me with more specific code than the article provides?  I am just not sure how to tweak their code to my unique situation. Thank you so much in advance!

---

@TomMartens wrote:

Hey,

the challenge you are facing has a name (know your enemy 🙂 ), it's called semi-additive measure.

This article describes almost anything that will surface over time tha's time related: https://www.daxpatterns.com/time-patterns/

Watch for the semi-additve pattern.

Hopefully this provides the insights you are looking for.

Regards,

Tom

---

[TomMartens]

Hey Angel,

please provide a pbix file that contains sample data but represents your data model (including existing measures and calculated columns), upload the pbix file to onedrive or dropbox and share the link. If you use an Excel file for the sample data, then also upload the xlsx and share the link also.

Regards,

Tom

[Anonymous]

@TomMartens  Thanks for the offer, unfortunately, I don't have access to one drive or drop box, thought I might be able to load a file here.

[Anonymous]

I am unable to provde a sample, are you able to help with just the information I've provided?

[Anonymous]

Just tried the semi-additive measure on my own and it worked the 1st time like a charm! How many times does that that happen? Thank you !!!

[Anonymous]

Tom- Thanks so much for the prompt response and sharing the article! I'm sure it contains the solution I need, but I'm not skilled enough to know how to apply the DAX to my specific situation.  I simply have the month as the column headers with drill downs for Quarter and Year and I have the Balances in the rows.  So for example: My balances in March were 1,000,000, my balances in April were 2,000,000 and my balances in May were 3,000,000.  When I drill to the Quarter and Year, I want my balances to show 3,000,000, summarized for the most recent month only, but instead it's summing the balances across all of the months and returning 6,000,000.  Also, If I filter for a previous month, I would need it to summarize YTD, QTD, etc. for whatever month I've filtered on.  Are you able to provide me with more specific code than the article provides?  I am just not sure how to tweak their code to my unique situation. Thank you so much in advance!