YoY Calculations DAX formula Help

[MichaelB_MO]

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

[v-xingshen-msft · Accepted solution]

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.

[adudani]

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