Forum Discussion
Year over Year calculation
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.
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:
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.
Regards
Thank very much! With metrics all works great!
Michael_Shao : Thank you for your addition. It was helpful.
- YoYVisits = [SumVisits]/[VisitLastyear]-1 Why do we use -1?
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!
Brilliant thank you!
Hello,
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.
Thank you.
