Forum Discussion

MichaelB_MO's avatar
MichaelB_MO
Regular Visitor
1 year ago
Solved

YoY Calculations DAX formula Help

Good morning,

I am trying to develop a YoY view.  I am currently utilizing the following formula:

Previous Year Calls = calculate (distinctcount('Database 1'[Incident Number]), dateadd('Calendar Table'[Date].[date],-1,year))

Here is how the visual appears:

When I use this code:

Previous Year Calls 2 = var cycalls = distinctcount(Database 1[Incident Number])
var pycalls = calculate (distinctcount('Database 1'[Incident Number]), previousyear('Database 1'[Dispatched Date]))
var check = cycalls <> blank() && pycalls <> blank()
return if(check,divide(cycalls,pycalls)-1)
Returns the same results.
 
I am looking to be able to have 2023 values appear next to 2024, 2022 next to 2023, 2021 next to 2022 and 2020 next to 2021.  
 
Any assistance would be greatly appreciated.  
 
Michael
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi MichaelB_MO ,

    You want to calculate the growth rate of each year compared to the previous year, right? Here's an example of how I've used the sample data to help you.

    We first create a YEAR column so that we can do a better calculation, then we sum each year separately and do a year-over-year growth rate calculation.

    Current Year Calls = 
 CALCULATE(DISTINCTCOUNT('Table'[Incident Number]),ALLEXCEPT('Table','Table'[Year]))
    Previous Year Calls = 
CALCULATE(
    DISTINCTCOUNT('Table'[Incident Number]),
    FILTER(
        ALL('Table'),
        'Table'[Year] = MAX('Table'[Year]) - 1
    )
)
    YoY Growth Rate = 
VAR CurrentCalls = [Current Year Calls]
VAR PreviousCalls =[Previous Year Calls]
RETURN
    IF(
        NOT(ISBLANK(CurrentCalls)) && NOT(ISBLANK(PreviousCalls)),
        DIVIDE(CurrentCalls - PreviousCalls, PreviousCalls, 0),
        BLANK()
    )

    If you have further questions, you can check my pbix file or feel free to reply to me, I will get back to you as soon as I hear from you, I would be honored if my solution solves your problem!

    Hope it helps!

     

    Best regards,
    Community Support Team_ Tom Shen

     

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

     

     

2 Replies

  • adudani's avatar
    adudani
    Memorable Member
    1 year ago

    hi MichaelB_MO ,

     

    this can be achieved with calculation groups. 

    Once you create the TY and LY calculation items, add the "Fiscal/ calendar year" in the columns of the matrix. 

     

    this should provide the intended result. for reference: Dynamic YTD Metric in Power BI Using Field Parameters and Calculation Groups 


    if this doesn't resolve the issue, kindly provide a sample input and output masking sensitive information in a usable format ( excel, csv, link to pbix etc.)

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi MichaelB_MO ,

    You want to calculate the growth rate of each year compared to the previous year, right? Here's an example of how I've used the sample data to help you.

    We first create a YEAR column so that we can do a better calculation, then we sum each year separately and do a year-over-year growth rate calculation.

    Current Year Calls = 
 CALCULATE(DISTINCTCOUNT('Table'[Incident Number]),ALLEXCEPT('Table','Table'[Year]))
    Previous Year Calls = 
CALCULATE(
    DISTINCTCOUNT('Table'[Incident Number]),
    FILTER(
        ALL('Table'),
        'Table'[Year] = MAX('Table'[Year]) - 1
    )
)
    YoY Growth Rate = 
VAR CurrentCalls = [Current Year Calls]
VAR PreviousCalls =[Previous Year Calls]
RETURN
    IF(
        NOT(ISBLANK(CurrentCalls)) && NOT(ISBLANK(PreviousCalls)),
        DIVIDE(CurrentCalls - PreviousCalls, PreviousCalls, 0),
        BLANK()
    )

    If you have further questions, you can check my pbix file or feel free to reply to me, I will get back to you as soon as I hear from you, I would be honored if my solution solves your problem!

    Hope it helps!

     

    Best regards,
    Community Support Team_ Tom Shen

     

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.