Forum Discussion
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?
Hello,
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.
17 Replies
Hello,
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.
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.
Hi Victor,
Thank you very much for your response :smileyhappy: 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))
Time_Traveler Are you creating MEASURES? All 3 of Vvelarde's formulas should be MEASURES! :smileyhappy:
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!
You can use the SAMEPERIODLASTYEAR function:
LastYearSales = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))
Hello,
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))
I am having the exact same issue.
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 DAX
There’s a custom visual that lets you compare YoY variance without writing any DAX — all through simple drag-and-drop actions using the Pivot button.
It also includes three powerful tools: Analytics, Column Visibility Management, and Visual Comparison Charting.
Check out the video for details https://www.youtube.com/watch?v=hVmlZp7qh3YLink to visual: https://flexaintel.com/flexa-tables
