Trailing 12 Month YoY % Comparison

[pmstahl]

Goal: Show a percentage by month that compares the rolling 12 month sum to the prior year's 12 month sum. 

Examples:
For 2024-06, the current 12 month sum is 3195. The previous year's 12 month sum is 1800. 
3195 / 1800 - 1 = 78% growth

For 2024-05, the 12 month sum 3125. The previous year's 12 month sum is 1483.

3125 / 1483 - 1 = 88% growth

Then plot the % growth across time being the x-axis:

CHALLENGE/ISSUE: Based on my available data in PowerBI, I want to show the graph from 2023-05 to 2024-06 but when I filter out the year-month I don't want on the visual, I lose the data from those months and the 12 month sums get messed up in the filter context. 

I have watched some videos that duplicate the date table to use a slicer context and then incorporate the slicer dates filter context in the calculate function but nothing I have tried can produce the results I am looking for. 

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[v-nuoc-msft · Accepted solution]

Hi @pmstahl 

According to your description, the result is incorrect due to data loss after using the filter. This may have something to do with the filter criteria you used when creating measure.

To ensure that the calculation is not affected by the slicer, use or ignore the slicer context in the measure. For example,

All: Ignore all filters.

Measure = CALCULATE(SUM('Table'[values]), ALL('Table'))

If you still have problems, it is best to provide the pbix file and be careful to delete sensitive data.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.