cancel
Showing results for
Did you mean:
Regular Visitor

## 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?

1 ACCEPTED SOLUTION
Community Champion

@Time_Traveler

Hello,

To Visits

SumVisits= Calculate([Sum(Table[Visits]))

YoYVisits = [SumVisits]/[VisitLastyear]-1

To PageViews only change Visits column to Page Views Column.

Lima - Peru
16 REPLIES 16
Anonymous
Not applicable

Dateformat is given in YEAR,  when I changed it to mm/dd/yy,it is showing another data. So for year specifically any formula is there

New Member

You can use the SAMEPERIODLASTYEAR function:

LastYearSales = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))

Helper I

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

New Member

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.

YOY Calculation Issue in PowerPivot DAX

Helper I

I have the same issue here... can any expert kindly share the solution?

My data set looks like below...

As you can see, the last_yr_unit column is all empty from 2007 t0 2014, expect "total" row.

New Member

I am having the exact same issue.

Community Champion

@Time_Traveler

Hello,

To Visits

SumVisits= Calculate([Sum(Table[Visits]))

YoYVisits = [SumVisits]/[VisitLastyear]-1

To PageViews only change Visits column to Page Views Column.

Lima - Peru
New Member

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.

Regular Visitor

Brilliant thank you!

Anonymous
Not applicable

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.

Anonymous
Not applicable
YoYVisits = [SumVisits]/[VisitLastyear]-1 Why do we use -1?
Community Champion

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!

Regular Visitor

Hi Victor,

Thank you very much for your response  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 ]))

Microsoft

Hi Time_Traveler,

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:

Regards

Regular Visitor

Thank very much! With metrics all works great!

Community Champion

@Time_Traveler Are you creating MEASURES? All 3 of @Vvelarde's formulas should be MEASURES!

Announcements

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

#### What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.