Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Time_Traveler
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
Vvelarde
Community Champion
Community Champion

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




Lima - Peru

View solution in original post

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

ShmulikTal
New Member

You can use the SAMEPERIODLASTYEAR function:

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

mcornejo_1
Helper I
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]))

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

 

YoY.jpg

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 DAXYOY Calculation Issue in PowerPivot DAX

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

 

My data set looks like below...

 

prom_1.JPG

 

 

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.

 

prom_2.JPG

 

 

Thank you for advance! 

 

 

 

I am having the exact same issue.

Vvelarde
Community Champion
Community Champion

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




Lima - Peru

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.

YoY trend.PNG

Thank you.

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?
Sean
Community Champion
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!

Hi Victor,

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

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:

5.PNG

Please reply back if you need any further help.

Regards

Thank very much! With metrics all works great!

 

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.