12 month growth (not rolling average) % calculation and visual

shiex1 — Wed, 13 Dec 2023 23:55:29 GMT

Hello, I have been working on some calculations this week and I wonder if anyone knows why the _12moOffsetGrowth has the _% overall and how is that calculated by PBI? I need the measure to show -0.63% on the card visual instead of -87.41% since the date slicer (on Billing Cycle, which has a relationship with date table) is set to have the most recent month to be Dec 2023. If the date slicer is set to have the most recent month to be Oct 2023, then the card visual should say 92.93%. Below are what i did to get to this point and sample data. Any thoughts are appreciated. Thank you.

I created the _12moOffsetCharge as:

_12moOffsetCharge = CALCULATE(SUMX(Data_Table, Data_Table[Actual Charge]),PARALLELPERIOD('DATE Table'[Date], -11,MONTH))

The MaxMonth Actual Charge is created measure as below so when the filter of month year changes, the max charge changes:

_MaxMonthEffharge =

VAR __MaxYear = MAX('DATE Table'[Year])

VAR __MaxMonth = MAX('DATE Table'[Month])

VAR __TmpTable = CALCULATETABLE('Data Table',ALL('DATE Table'[Year]),All('DATE Table'[Month]))

RETURN SUMX(FILTER(__TmpTable,Data Table[_BillingYear]=__MaxYear && Data Table[_BillingMonth] = __MaxMonth),Data Table[Actual Charge])

Lastly I calculate the 12mo growth measure as:

_12moOffsetGrowth% = DIVIDE([_MaxMonthEffharge] - [_12moOffsetCharge], [_12moOffsetCharge])

I need

Here is the data.

Year	Month	Actual Charge	Prior Billing Cycle Charge	MoM Variance	MoM Variance %	_YTD MoM % (Based on Discounted Gross Charge)	12-mo Avg % Change	_MaxMonthEffharge	_12moOffsetCharge	_12moOffsetGrowth%
2022	May	$ 17,885.07		17,885.07				17,885.07
2022	June	$ 146,414.81	17,885.07	128,529.74	718.64%	718.64%	718.64%	146,414.81
2022	July	$ 142,042.75	146,414.81	(4,372.06)	-2.99%	357.83%	357.83%	142,042.75
2022	August	$ 212,896.38	142,042.75	70,853.63	49.88%	255.18%	255.18%	212,896.38
2022	September	$ 207,696.94	212,896.38	(5,199.44)	-2.44%	190.77%	190.77%	207,696.94
2022	October	$ 174,485.42	207,696.94	(33,211.52)	-15.99%	149.42%	149.42%	174,485.42
2022	November	$ 201,927.65	174,485.42	27,442.23	15.73%	127.14%	127.14%	201,927.65
2022	December	$ 229,243.29	201,927.65	27,315.64	13.53%	110.91%	110.91%	229,243.29
2023	January	$ 193,298.78	229,243.29	(35,944.51)	-15.68%	-15.68%	95.09%	193,298.78
2023	February	$ 217,615.94	193,298.78	24,317.16	12.58%	-1.55%	85.92%	217,615.94
2023	March	$ 212,483.01	217,615.94	(5,132.94)	-2.36%	-1.82%	77.09%	212,483.01
2023	April	$ 306,524.11	212,483.01	94,041.11	44.26%	9.70%	74.11%	306,524.11	17,885.07	1613.86%
2023	May	$ 300,219.67	306,524.11	(6,304.45)	-2.06%	7.35%	67.76%	300,219.67	146,414.81	105.05%
2023	June	$ 277,168.01	300,219.67	(23,051.66)	-7.68%	4.84%	7.23%	277,168.01	142,042.75	95.13%
2023	July	$ 282,204.95	277,168.01	5,036.94	1.82%	4.41%	7.63%	282,204.95	212,896.38	32.56%
2023	August	$ 329,822.24	282,204.95	47,617.29	16.87%	5.97%	4.88%	329,822.24	207,696.94	58.80%
2023	September	$ 365,386.93	329,822.24	35,564.70	10.78%	6.50%	5.98%	365,386.93	174,485.42	109.41%
2023	October	$ 389,575.77	365,386.93	24,188.83	6.62%	6.52%	7.87%	389,575.77	201,927.65	92.93%
2023	November	$ 541,835.81	389,575.77	152,260.05	39.08%	9.48%	9.81%	541,835.81	229,243.29	136.36%
2023	December	$ 192,081.36	541,835.81	(349,754.45)	-64.55%	3.31%	3.31%	192,081.36	193,298.78	-0.63%

Re: 12 month growth (not rolling average) % calculation and visual

lbendlin — Sun, 17 Dec 2023 00:04:09 GMT

Would you mind explaining the calculation rules for the columns startign with YTD Average % Change?

By the way here is the simplified PBIX for the left side.

topic Re: 12 month growth (not rolling average) % calculation and visual in DAX Commands and Tips

12 month growth (not rolling average) % calculation and visual

Re: 12 month growth (not rolling average) % calculation and visual