Hi Pigment community,
My case description:
I have 3 Transaction List [tl], which have accounting transactions, I added to those list identical dimension.
Then I have created a “Staging Metric” that’s adding up for 3 [tl] the column “USD Net value” (number property), on the following dimensions [by: Company, ‘Company and Account’, Month]
Note: ‘Company and Account’ dimension is the lowest level and from where stem the Reporting dimensions (Level 1, Level 2, Level 3).
The metric aggregates well, and adds up the transaction values at each reporting level and at each time level (month, year, quarter).
Then I want to create a metric that does the cumulative for “USD Net value”, so I can get the values for the “Balance Sheet” cumulating on a monthly basis. I use the following syntax:
- Cumulate('Staging Metric'[Filter:'Company and Account’.Report = Report."Balance Sheet"],Month)
- Cumulate('Staging Metric',Month)
The formula works well when showing Month Dimension in the columns
The problem: When showing the Years only the numbers doesn’t add up.
How should I approach the problem to get proper cumulative figures on a ‘Year’ dimension too?
Thanks in advance for your help here ;D
Best answer by Matt DView original