Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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) base...
Need Help
  • audreygerred's avatar
    audreygerred
    2 years ago

    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))
        RETURN
            DIVIDE([Total Cost] - __PREV_YEAR, __PREV_YEAR)
    )

     

    DIVIDE function (DAX) - DAX | Microsoft Learn

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
audreygerred
Icon for Super User rankSuper User
2 years ago

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))
    RETURN
        DIVIDE([Total Cost] - __PREV_YEAR, __PREV_YEAR)
)

 

DIVIDE function (DAX) - DAX | Microsoft Learn

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    That did it! Thank you so much for being so thorough and providing sources. Tremendous help. 

    • audreygerred's avatar
      audreygerred
      Icon for Super User rankSuper User
      2 years ago

      You're very welcome! I'm happy to help!