Forum Discussion
Line and Column Chart - YoY Trend Lines?
I am a brand new user to the platform, so apologies in advance if I've missed something obvious. I am attempting to set up a column chart showing total expenses per period (year, month, quarter) based on a selected time range. While this part isn't an issue, I am also attempting to set up a trend line that will run through the chart whlie showing YoY change from the previous year's matching unit (January 2023 vs. January 2022).
I've set up measures that calculate the total expenses for the selected period, total expenses for the same period from the previous year, and a third that calculates YoY percentage increase or decrease based on the previous two. Everything seems to work fine with those, numbers all match up with what they should be. However, when setting that up as the line y-axis variable, my line appears as below:
I can see the issue is that it's taking these double digit percentages and showing them as integers, which on a y-axis scaling into the tens of thousands essentially has them all down at 0. Is there a way for me to fix this? I assume this isn't data related and rather an option I'm not aware of in the visualization settings, but I can provide further info if needed.
Awesome - thank you! Anytime you do division, use the DIVIDE DAX function, you will have better performance. For the YoY% you can utilize the quick measure. In your data pane, go to your measure Total Cost, click the 3 dots, click New quick measure.
Once that selection opens up you can choose the Year-over-year-change option:
And, add your date from the date table to the date well, then click add:
You'll wind up with DAX measure with this for the DAX (assuming your date table is marked as a date table, if it's not then your date portion of the measure will have 'Date'[date].[Date]) :
Total Cost YoY% =IF(ISFILTERED('Date'[date]),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([Total Cost], DATEADD('Date'[date], -1, YEAR))RETURNDIVIDE([Total Cost] - __PREV_YEAR, __PREV_YEAR))
5 Replies
- audreygerred
Super User
Can you share your DAX for the YoY measure? Also, I would suggust creating an explicit measure for Cost instead of using the implicit one currently in the visual.
http://powerbiwithme.com/2023/10/25/the-explicit-implicit-measure-edition/
Thank you for the resource! I forgot to change it over to the one I've calculated, so thanks for the reminder on that as well.
These are the 3 DAX functions at play here :Total Cost = SUM('2022 - 2024 Expenses'[Cost])Cost LY = CALCULATE([Total Cost], DATEADD('Calendar'[Date], -1, YEAR))Cost YoY = ((([Total Cost] - [Cost LY]) / [Cost LY]))- audreygerred
Awesome - thank you! Anytime you do division, use the DIVIDE DAX function, you will have better performance. For the YoY% you can utilize the quick measure. In your data pane, go to your measure Total Cost, click the 3 dots, click New quick measure.
Once that selection opens up you can choose the Year-over-year-change option:
And, add your date from the date table to the date well, then click add:
You'll wind up with DAX measure with this for the DAX (assuming your date table is marked as a date table, if it's not then your date portion of the measure will have 'Date'[date].[Date]) :
Total Cost YoY% =IF(ISFILTERED('Date'[date]),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([Total Cost], DATEADD('Date'[date], -1, YEAR))RETURNDIVIDE([Total Cost] - __PREV_YEAR, __PREV_YEAR))