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.

Time_Traveler's avatar
Time_Traveler
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))

v-micsh-msft's avatar
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's avatar
    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.