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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

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%
1 REPLY 1
Super User

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.

## Helpful resources

Announcements

#### Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors