Year over Year in Line and Column Chart

[harrisonearl]

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.

[camargos88]

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

[harrisonearl]

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

[camargos88]

@harrisonearl ,

Do you have some data to use as example ?

Ricardo

[harrisonearl]

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

Date	Passengers
1/1/2018	68811
2/1/2018	67205
3/1/2018	81203
4/1/2018	81989
5/1/2018	97435
6/1/2018	102286
7/1/2018	100199
8/1/2018	91499
9/1/2018	87045
10/1/2018	102181
11/1/2018	93455
12/1/2018	90185
1/1/2019	76377
2/1/2019	74317
3/1/2019	98502
4/1/2019	91783
5/1/2019	113905
6/1/2019	119654
7/1/2019	123378
8/1/2019	110506
9/1/2019	102927
10/1/2019	116707
11/1/2019	101891
12/1/2019	106283
1/1/2020	89461

[Anonymous]

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]

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

[camargos88 · 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