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
dp_75
Frequent Visitor

Comparing data to a base year, calculate difference and % change

Hi there,

I have data value for each date 2019-2024, and I want to show monthly  average and % change vs the average from same month in 2019 (as a base year).  I have a date column (date format) and a year column (number format). I am using the following formula:

 

Average for each month: This shows the average for whole of 2019 year , not the specific month in 2019 ?

Calc 2019 Avg = CALCULATE(Average('All data'[Value]),FILTER(ALLSELECTED('All data'),    'All data'[Date] in FILTERS('All data'[Date])&&'All data'[Year]=2019))
 
% change for each month: This is showing incorrect % change (as it is referencing the average for the whole of 2019)
Avg Value vs 2019 avg = Avg Value vs Avg 2019 = divide(Average('All data'[Value]),CALCULATE(Average('All data'[Value]),FILTER(allselected('All data'),    'All data'[Date] in FILTERS('All data'[Date])&&'All data'[Year]=2019))        )-1
 
 
dp_75_0-1729169856942.png

 

Anyh ideas on how i can pull back the average for each month and a % growth compared to the same month in 2019 ?
 
Thanks !
dp_75 
1 ACCEPTED SOLUTION
Anonymous
Not applicable

lbendlin , thanks for your concern about this case. I tried to create a sample data myself based on the user's requirement and implemented the result. Please check if there is anything that can be improved. Here is my solution:


Hi  @dp_75 ,

I created some data:

vyangliumsft_0-1729219106806.png

Here are the steps you can follow:

1. Create measure.

Calc 2019 Avg =
AVERAGEX(
    FILTER(ALL('Table'),'Table'[Year]=2019&&'Table'[Month]=MAX('Table'[Month])),[Value])
Avg Value vs 2019 avg =
DIVIDE(
AVERAGEX('Table',[Value]),[Calc 2019 Avg])-1

2. Result:

vyangliumsft_1-1729219106809.png

 

Best Regards,

Liu Yang

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

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

lbendlin , thanks for your concern about this case. I tried to create a sample data myself based on the user's requirement and implemented the result. Please check if there is anything that can be improved. Here is my solution:


Hi  @dp_75 ,

I created some data:

vyangliumsft_0-1729219106806.png

Here are the steps you can follow:

1. Create measure.

Calc 2019 Avg =
AVERAGEX(
    FILTER(ALL('Table'),'Table'[Year]=2019&&'Table'[Month]=MAX('Table'[Month])),[Value])
Avg Value vs 2019 avg =
DIVIDE(
AVERAGEX('Table',[Value]),[Calc 2019 Avg])-1

2. Result:

vyangliumsft_1-1729219106809.png

 

Best Regards,

Liu Yang

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

many thanks - that solution works.

I also found this similar DAX expression works:

 

Avg Value 2019 = CALCULATE (
    AVERAGE ( 'All data'[Value] ),
    FILTER (
        ALL ( 'All data' ),
        'All data'[Date].[Month]
            = MAX ( 'All data'[Date].[Month] )
            && 'All data'[Date].[Year] = 2019 ))

 

 

lbendlin
Super User
Super User

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.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Thanks Ibendlin - i think we've got the answer here.  I will add a sample file next time as you suggest - much easier to deal with.

regards

DP_75

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.