YTD and Rolling Average by Month measure incorrect when selecting months from multiple years

[shiex1]

Hello Community, I’m building a DAX to calculate the YTD average monthly % change over one type of cost that occurs multiple times a day year around. I have calculated couple measures and have the below table:

1. _PriorMonthActualCharge = CALCULATE(SUM([Actual Charge]), PARALLELPERIOD([Billing Cycle].[Date],-1,MONTH))
2. _MoMVariance = SUM([Actual Charge])-[ _PriorMonthActualCharge]
3. _MoMVar% = DIVIDE([_MoMVariance],[_PriorMonthActualCharge])

With the above measures, I was able to summarize the data into below table:

Year	Month	Actual Charge	_Prior Month Charge	_MoM Variance	_MoM Var%
2022	September	$207,696.94	$212,896.38	($5,199.44)	-2.44%
2022	October	$174,485.42	$207,696.94	($33,211.52)	-15.99%
2022	November	$201,927.65	$174,485.42	$27,442.23	15.73%
2022	December	$229,143.44	$201,927.65	$27,215.79	13.48%
2023	January	$192,524.95	$229,143.44	($36,618.49)	-15.98%
2023	February	$216,842.11	$192,524.95	$24,317.16	12.63%
2023	March	$211,785.10	$216,842.11	($5,057.01)	-2.33%
2023	April	$305,750.28	$211,785.10	$93,965.18	44.37%

The next measure is where I encountered issues with. I created the “_YTD % Change” measure as below:

**note that Billing Cycle below is a Date column.

_YTD % Change =  

var NumOfMonths = MONTH(MAX([Billing Cycle]))

var MaxCurrentDate = MAX([Billing Cycle])

var Period = DATESINPERIOD([Billing Cycle], MaxCurrentDate, -NumOfMonths, MONTH)

var Result = CALCULATE(AVERAGEX(VALUES([Billing Cycle].[Month]),[_MoMVar%]), Period)

Return Result

After adding in the “_YTD % Change” measure, the calculation is right only if the billing cycle filter applied on the report is within the current year (in this case 2023).

If I add billing cycle 2022 Sep – Dec, the “_YTD % Change” will read as -69.49%. However, the pivot table subtotal for 2023 and 2022 respectively is right, just not the overall. 

I’d expect the “_ YTD % Change” measure to stay consistent calculating 2023 YTD data despite the additional selection of 2022 months. However, it seems that is not the case currently.

Another similar measure of “12-mo rolling monthly average % change” is also created and has the same behavior. Below is the DAX for the 12-mo rolling average % change.

_12moRolling% =

var NumOfMonths = 12

var MaxCurrentDate = MAX([Billing Cycle])

var Period = DATESINPERIOD([Billing Cycle], MaxCurrentDate, -NumOfMonths, MONTH)

Var Result = CALCULATE(AVERAGEX(VALUES([Billing Cycle].[Month]),[_MoMVar%]), Period)

Return Result

I hope to get the overall YTD on the card visual to show 18.22% no matter whether 2022 months are selected or not. I haven't found any solutions on the forum that works in this situation, and if you i have missed anything, please let me know. I really appreciate any help here.

Thank you!

[shiex1 · Accepted solution]

I figured out myself after more research =] 

Here is what i did:

_YTD% =

 CALCULATE(AVERAGEX(VALUES('DATE Table'[Date].[Month]),[_MoMVar%]), FILTER(ALL('DATE Table'[Date]), 'DATE Table'[Date] <= MAX([Billing Cycle]) && YEAR('DATE Table'[Date]) = YEAR(MAX([Billing Cycle]))))