Forum Discussion

Regular Visitor

9 years ago

Solved

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

9 years ago

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.

Regular Visitor

9 years ago

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))

Sean
Community Champion
9 years ago
Time_Traveler Are you creating MEASURES? All 3 of Vvelarde's formulas should be MEASURES! :smileyhappy:
v-micsh-msft
Microsoft Employee
9 years ago
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
- Time_Traveler
  Regular Visitor
  9 years ago
  Thank very much! With metrics all works great!
  
  Michael_Shao : Thank you for your addition. It was helpful.