Year over Year calculation

[Time_Traveler]

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?

[Anonymous]

---

---

---

---

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

[ShmulikTal]

You can use the SAMEPERIODLASTYEAR function:

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

[mcornejo_1]

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

RevLastYear = CALCULATE([SumRevenue],DATEADD(DATESYTD(Shipments[Date]),-1,YEAR))

[mathapatipooja]

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.

I also tried using DATEADD function with -1 for 'Year' Interval. It leads to the same 2 issues pointed above. Please help.

YOY Calculation Issue in PowerPivot DAX

[ryan1982]

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

My data set looks like below...

last_yr_unit = CALCULATE([ttl_unit],DATEADD(Sheet2[Yr],-1,YEAR))

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

Thank you for advance! 

[DWhitaker]

I am having the exact same issue.

[Vvelarde · Accepted solution]

@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.

[praju]

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.

[twkyoscr]

Brilliant thank you!

[Anonymous]

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]

YoYVisits = [SumVisits]/[VisitLastyear]-1 Why do we use -1?

[Sean]

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!

[Time_Traveler]

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

VisitLastYear = CALCULATE([SumVisits]; DATEADD(Table[Date];-1;YEAR))

[v-micsh-msft]

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]

Thank very much! With metrics all works great!

Michael_Shao : Thank you for your addition. It was helpful.

[Sean]

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