cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Year over Year in Line and Column Chart

I'm struggling to figure out how to show a year over year line as part of a clustered column graph when I have a partial year. In the image below, I have monthly data from January 2018 through January 2020. Everything displays properly if I only have 24 months of data, but the 25th month (Jan 2020) breaks the year over year calculation (the line is displaying an incorrect value, and the other 11 months show as zero).

 

Any ideas on how to fix this so that it can show the actual last 12 months year over year? I'd like January 2020 to be compared to January 2019, February 2019 compared to February 2018, March 2019 compared to March 2018, and so on.

 

My data is super simple, a date field (one per month) in one column and a number of passengers in the second column.

 

PowerBI Chart.png

1 ACCEPTED SOLUTION

Hi @harrisonearl ,

 

I've create this file as an example: Download PBIX 

 

If you have more than 1 value for the same item in x-axis, the calculation gets all the values for that item...that's why you are getting the wrong value.

 

Perhaps it's better to have a chart with dates on a-axis instead of month...so you can see it by the time..or a chart without passenger numbers and just the values of the YoY....

 

I realized that column values handles it differently from Line values...

 

I hope it helps or someone can bring a better solution for it.

 

Ricardo



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

7 REPLIES 7
Super User III
Super User III

Hi @harrisonearl ,

 

Do you have a date table ? Are you using the function SAMEPERIODLASTYEAR ?

 

https://docs.microsoft.com/en-us/dax/sameperiodlastyear-function-dax

 

Ricardo



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



@camargos88 My data only has one line for each month, so it doesn't seem to want to let me use a date table. My YoY caluclation is the built in one, SAMEPERIODLASTYEAR doesn't seem to work since the dates aren't continous since I only have one for each month.

 

Passengers YoY% = 
IF(
	ISFILTERED('Sheet2'[Year and Month]),
	ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
	VAR __PREV_YEAR =
		CALCULATE(
			SUM('Sheet2'[Passengers]),
			DATEADD('Sheet2'[Year and Month].[Date], -1, YEAR)
		)
	RETURN
		DIVIDE(SUM('Sheet2'[Passengers]) - __PREV_YEAR, __PREV_YEAR)
)

 

@harrisonearl ,

 

Do you have some data to use as example ?

 

Ricardo



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



@camargos88 Here's the data I'm working with, nothing too complicated.

 

DatePassengers
1/1/201868811
2/1/201867205
3/1/201881203
4/1/201881989
5/1/201897435
6/1/2018102286
7/1/2018100199
8/1/201891499
9/1/201887045
10/1/2018102181
11/1/201893455
12/1/201890185
1/1/201976377
2/1/201974317
3/1/201998502
4/1/201991783
5/1/2019113905
6/1/2019119654
7/1/2019123378
8/1/2019110506
9/1/2019102927
10/1/2019116707
11/1/2019101891
12/1/2019106283
1/1/202089461
Anonymous
Not applicable

What you can do is... attached in the file. And it's not true you cannot use a proper calendar and time-intel functions. You can but you have to know how. The model in the file shows you the correct way.

 

Best

D

Anonymous
Not applicable

What you are trying to create is incorrect. This chart type cannot show what you want. There is only one line that can be shown on such a chart and you'd need to be able to show 3 of them (for each year). This is not what is happening. What you get is a total across all selected years which is totally wrong.

This chart simply can't show what you want.

Best
D

Hi @harrisonearl ,

 

I've create this file as an example: Download PBIX 

 

If you have more than 1 value for the same item in x-axis, the calculation gets all the values for that item...that's why you are getting the wrong value.

 

Perhaps it's better to have a chart with dates on a-axis instead of month...so you can see it by the time..or a chart without passenger numbers and just the values of the YoY....

 

I realized that column values handles it differently from Line values...

 

I hope it helps or someone can bring a better solution for it.

 

Ricardo



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session Drive Data Culture with Power BI: Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors