Forum Discussion

Time_Traveler's avatar
Time_Traveler
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?
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    Time_Traveler

     

    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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago
YoYVisits = [SumVisits]/[VisitLastyear]-1 Why do we use -1?
Sean's avatar
Sean
Community Champion
6 years ago

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!