Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
shiex1
Frequent Visitor

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

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:

YearMonthActual Charge_Prior Month Charge_MoM Variance_MoM Var%
2022September$207,696.94$212,896.38($5,199.44)-2.44%
2022October$174,485.42$207,696.94($33,211.52)-15.99%
2022November$201,927.65$174,485.42$27,442.2315.73%
2022December$229,143.44$201,927.65$27,215.7913.48%
2023January$192,524.95$229,143.44($36,618.49)-15.98%
2023February$216,842.11$192,524.95$24,317.1612.63%
2023March$211,785.10$216,842.11($5,057.01)-2.33%
2023April$305,750.28$211,785.10$93,965.1844.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).

shiex1_1-1702314038049.png

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. 

shiex1_0-1702314001299.png

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!

1 ACCEPTED SOLUTION
shiex1
Frequent Visitor

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]))))

View solution in original post

1 REPLY 1
shiex1
Frequent Visitor

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]))))

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.