DAX Cumulative sum by Month/Quarter/Year but only include contributing months

[craigdormire]

I need help modifying/creating a measure to correctly aggregate so that when monthly results are rolled up to quarterly and yearly, only values from aged months contribute to the total.  What rolls up to the total should be controlled by the age of the contributing months.  

Here's the current DAX measure:

Units (Cuml. by Age) =

var Max_Age = max('Monthly_Data'[Age])

return

CALCULATE(

[Units (Non-Cuml.)],

    'Monthly_Data'[Age]<=Max_Age

 )

This measure works for the Cumulative Sum at the monthly level, but as you can see below, because of the <=Max_Age logic, the quarterly and yearly totals are adding all prior months, not just the vintages that are aged to that point. 

So, you can see that January is the only "fully aged" month, so that should be the only value "contributing" to Q1 and for 2023 at Age '8'.  Basically, I need it to sum up the column:

-  at point 8, the monthly, Q1 and 2023 cumulative total should all be '366'

- at point 7, the Q1 and 2023 totals should be '533'

- at point 5, the Q1 total should be '313' but the 2023 total should be '444' (Q1 + April) 

Since I haven't gotten this approach to work, I've thought about a 2 step approach, where I calculate the cumulative sum/running total locked in at the monthly level by age, then just sum the monthly cumulative value by quarter and year at each age.  But I haven't really figured out how to make this concept work either.

Thanks for any help/ideas you can provide!

[craigdormire · Accepted solution]

I believe my internal company PBI User Group has found a solution.  See below:

Units (Cuml. by Age) =

var Max_Age = max('Monthly_Data'[Age])

var Valid_Months = CALCULATETABLE (

  DISTINCT ( 'Monthly_Data'[Month] ),

  FILTER ( 'Monthly_Data', 'Monthly_Data'[Age] = Max_Age )

)

return

CALCULATE(

  [Units (Non-Cuml.)],

  'Monthly_Data'[Age]<=Max_Age,

  'Monthly_Data'[Month] IN Valid_Months

)

Thanks to anyone/everyone out there that took time to look at this with me!  Consider this one SOLVED!

[littlemojopuppy]

@craigdormire at first glance I would think you would also need a variable to determine fully aged months to include in your CALCULATE() statement.  Difficult to determine what the DAX should be without sample data...can you provide some?

[craigdormire]

Year	Quarter	Month	Units - Non-Cuml.	MSF
2023	Qtr 1	January	0	0
2023	Qtr 1	January	0	1
2023	Qtr 1	January	2	2
2023	Qtr 1	January	8	3
2023	Qtr 1	January	22	4
2023	Qtr 1	January	73	5
2023	Qtr 1	January	75	6
2023	Qtr 1	January	73	7
2023	Qtr 1	January	113	8
2023	Qtr 1	February	0	0
2023	Qtr 1	February	0	1
2023	Qtr 1	February	2	2
2023	Qtr 1	February	12	3
2023	Qtr 1	February	20	4
2023	Qtr 1	February	70	5
2023	Qtr 1	February	85	6
2023	Qtr 1	February	91	7
2023	Qtr 1	March	0	0
2023	Qtr 1	March	0	1
2023	Qtr 1	March	6	2
2023	Qtr 1	March	8	3
2023	Qtr 1	March	21	4
2023	Qtr 1	March	119	5
2023	Qtr 1	March	135	6
2023	Qtr 2	April	0	0
2023	Qtr 2	April	2	1
2023	Qtr 2	April	1	2
2023	Qtr 2	April	6	3
2023	Qtr 2	April	22	4
2023	Qtr 2	April	100	5
2023	Qtr 2	May	0	0
2023	Qtr 2	May	0	1
2023	Qtr 2	May	2	2
2023	Qtr 2	May	9	3
2023	Qtr 2	May	34	4
2023	Qtr 2	June	0	0
2023	Qtr 2	June	0	1
2023	Qtr 2	June	6	2
2023	Qtr 2	June	16	3
2023	Qtr 3	July	0	0
2023	Qtr 3	July	0	1
2023	Qtr 3	July	2	2
2023	Qtr 3	August	0	0
2023	Qtr 3	August	0	1
2023	Qtr 3	September	0	0