Current year sum compared to all previous years (Not cumulative)

[gbpbi]

Hello, I am trying to set up a measure that gives me a % difference of current sum of sales vs all previous year's. This explains roughly what I am aiming to do.

In this example 2024 is the current year, and the two rows below its sums are what I'm trying to figure a way to visualize in PowerBi. Now in 2025 there would be data for 2022-2025 so the visual I'm trying to make would be tracking % change of 2025 data from 2022, 2023, and 2024 separate from eachother.

[gbpbi · Accepted solution]

This was the measure that I used:

_Year % Diff to CY NIS =
VAR __Sales = [_NIS]
VAR __CYSales = CALCULATE([_NIS], DimDates[Year] = YEAR(TODAY()))
RETURN DIVIDE(__CYSales,__Sales,0) - 1

I then used "show values as rows" on the table settings and it got me what I was looking for. Thanks for the help.

[Anonymous]

Hi @gbpbi 
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[Anonymous]

Hi @gbpbi 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If their response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[Anonymous]

Hi @gbpbi 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[Anonymous]

Hi @gbpbi ,

First of all, I want to point out that your visual can not be fully implemented. It is not possible in Power BI to have a matrix with the original data in the top half and a few additional rows in the bottom half to calculate the percentage difference. You can only split the two parts and display them in two different matrices.

In addition, I have added a new table here for sorting the months. This table may not exist in your data. You can adjust it by yourself. It will not have a big impact.

Then use this DAX to create a measure:

%Change = 
VAR _maxyear = MAXX(ALL('Table'), 'Table'[Year])
VAR _maxyearvalue = 
CALCULATE(
    SUM('Table'[Value]),
    'Table'[Year] = _maxyear
)
VAR _averageperyear = 
CALCULATE(
    AVERAGE('Table'[Value]),
    ALL('Table'),
    'Table'[Year] = MAX('Table'[Year])
)
VAR _averagemaxyear = 
CALCULATE(
    AVERAGE('Table'[Value]),
    ALL('Table'),
    'Table'[Year] = _maxyear
)
RETURN
IF(
    ISFILTERED('Month_Sort'[Month]),
    (_maxyearvalue - SUM('Table'[Value])) / SUM('Table'[Value]),
    (_averagemaxyear - _averageperyear) / _averageperyear
)

And the final output is as below:

The Total part shows the %Change between the average values ​​of the Values ​​of the two years.

If you add the data for 2025 to the original data, the results returned by DAX will automatically change to a comparison between 2025 and other years without modifying DAX.

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

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...