Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
@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)
)
Do you have some data to use as example ?
Ricardo
@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 |
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |