The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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:
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!
Solved! Go to Solution.
I figured out myself after more research =]
Here is what i did:
I figured out myself after more research =]
Here is what i did:
User | Count |
---|---|
15 | |
8 | |
6 | |
6 | |
6 |
User | Count |
---|---|
23 | |
14 | |
13 | |
8 | |
8 |