Showing results for 
Search instead for 
Did you mean: 
Regular Visitor

Year over Year calculation

Hi guys,

I'm trying to make a calculation YoY for Page Views and Visits in my report and it doesn't work. Can you help me with formulas?

Community Champion
Community Champion




To Visits


SumVisits= Calculate([Sum(Table[Visits]))


VisitLastYear= Calculate([SumVisits],DATEADD(Table[Date],-1,YEAR))


YoYVisits = [SumVisits]/[VisitLastyear]-1


To PageViews only change Visits column to Page Views Column.

Lima - Peru

View solution in original post

Not applicable


Dateformat is given in YEAR,  when I changed it to mm/dd/yy,it is showing another data. So for year specifically any formula is there

New Member

You can use the SAMEPERIODLASTYEAR function:

LastYearSales = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))

Helper I
Helper I



I am relatively new to Power BI so I'm still fumbling through it.  I have attempted to use this technique in a report and continue to have an issue. As far as I can tell I have followed the steps outlined here. I should be seeing 2015 results in the RevLastYear column next to 2016 but instead only get the Total 30107315 and that is only because I turned on Total row otherwise I have a blank RevLastYear column. I hope I provided enough information to give you an idea of what I'm up against here. Do you think you can help?


Year = YEAR(Shipments[Date])

DateMonth = MONTH(Shipments[Date])

SumRevenue = CALCULATE(SUM(Shipments[Total Revenue]))

RevLastYear = CALCULATE([SumRevenue],DATEADD(DATESYTD(Shipments[Date]),-1,YEAR))



Facing the same issue... I am working on DAX in Excel Powerpivot

All the measures are created in table Calculation_Cube.


TotalUnits= SUM(Calculation_Cube[Units])

1 -> Total_Units_Previous_Year=calculate([Total Units],SAMEPERIODLASTYEAR(DateTable[Date])) - If i choose this I am eding up getting same values as in 'TotalUnits'

2 -> Units Previous Year=calculate([Total Units],SAMEPERIODLASTYEAR(Calculation_Cube[DateValue])) - If i choose this I am ending up with all blank.


I also tried using DATEADD function with -1 for 'Year' Interval. It leads to the same 2 issues pointed above. Please help.

YOY Calculation Issue in PowerPivot DAXYOY Calculation Issue in PowerPivot DAX

I have the same issue here... can any expert kindly share the solution?


My data set looks like below...





last_yr_unit = CALCULATE([ttl_unit],DATEADD(Sheet2[Yr],-1,YEAR))


As you can see, the last_yr_unit column is all empty from 2007 t0 2014, expect "total" row.





Thank you for advance! 




I am having the exact same issue.

Community Champion
Community Champion




To Visits


SumVisits= Calculate([Sum(Table[Visits]))


VisitLastYear= Calculate([SumVisits],DATEADD(Table[Date],-1,YEAR))


YoYVisits = [SumVisits]/[VisitLastyear]-1


To PageViews only change Visits column to Page Views Column.

Lima - Peru



I'm new to Power BI and still learning. I'm working on a report and I want to show the YoY calculation for Warranty costs. Can someone help me the initial steps? I went through some of the replies but couldn't go past step 1. I have attached a snip of the file that I'm working on.

YoY trend.PNG

Thank you.

Brilliant thank you!

Not applicable

In response to those getting blank values in the LastYear measure, I found the solution was to qualify the date field like so;

VisitLastYear= Calculate([SumVisits], SamePeriodLastYear(Table[Date].[Date]))

My guess is that the automatic date hierarchy introduced in 2018 is the cause of this and that you now need to specify the level in the hierarchy you want. I'm new to PowerBI so my explanation could be totally wrong. But this worked for me.

Not applicable

YoYVisits = [SumVisits]/[VisitLastyear]-1 Why do we use -1?
Community Champion
Community Champion

To get YoY you need the difference ( [SumVisits] - [VisitLastyear] ) divided by Last Year's number

I personally also have a Measure calculating this difference

Regardless... when you use - 1 you get the same result see below...

The basic formula is

( [SumVisits] - [VisitLastyear] ) / [VisitLastyear]

however this can be written as

( [SumVisits] [VisitLastyear] ) - (  [VisitLastyear] / [VisitLastyear] )

which can in turn be simplified as 

( [SumVisits] / [VisitLastyear] ) - 1


Division will be executed before subtraction so need for the parenthesis which I've included anyway


Hope this helps!

Hi Victor,

Thank you very much for your response Smiley Happy I've a problem on the last step.


YoYVisits = [SumVisits]/[VisitLastYear]-1

Err: Detected circular dependency Table[VisitLastYear], Table[YoYVisits], Table[VisitLastYear].


1-2 steps looks ok.

SumVisits = CALCULATE(SUM(Table[Visits ]))

VisitLastYear = CALCULATE([SumVisits]; DATEADD(Table[Date];-1;YEAR))

Hi Time_Traveler,


What is your current situation?

I tested with the formula posted by Victor, it works. And as posted by Sean, they should be all measures.

In addition, DATEADD function requires that we use the date column to be continuous, otherwise it will not work.

Besides, to have the YoYVisits works better, we could change the formula in the format below:

YoYVisits = if([VisitLastYear], [SumVisits]/[VisitLastyear]-1)

See the testing results:


Please reply back if you need any further help.


Thank very much! With metrics all works great!


Michael_Shao : Thank you for your addition. It was helpful.

Community Champion
Community Champion

@Time_Traveler Are you creating MEASURES? All 3 of @Vvelarde's formulas should be MEASURES! Smiley Happy

Helpful resources

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors