Year over Year metric calculation (I'm stumped!)

[Dvaudreuil]

Hey everyone-

I am trying to figure out the easiest way to calculate Year over Year growth on a simple matrix visual.  The ask from the business users is to simply see the YoY% growth for each month/year based on the filters they apply in the slicers at the top of the report (see below).  For example, the YoY% increase in January 2019 (501) and 2020 (473) should be -5.59%.  I would like to display the # in a card visual if possible.  Any help would be appreciated!

[v-kelly-msft · Accepted solution]

Hi @Dvaudreuil ，

I made a simple sample from part of your data:

Create a measure as below:

YoY% increase =
VAR _maxyear =
    CALCULATE ( MAX ( 'Table'[Year] ), ALLSELECTED ( 'Table'[Year] ) )
VAR _minyear =
    CALCULATE ( MIN ( 'Table'[Year] ), ALLSELECTED ( 'Table'[Year] ) )
RETURN
    IF (
        NOT ( ISFILTERED ( 'Table'[Month] ) ),
        DIVIDE (
            CALCULATE (
                SUM ( 'Table'[Value] ),
                FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Year] = _minyear )
            )
                - CALCULATE (
                    SUM ( 'Table'[Value] ),
                    FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Year] = _maxyear )
                ),
            CALCULATE (
                SUM ( 'Table'[Value] ),
                FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Year] = _minyear )
            )
        ),
        DIVIDE (
            CALCULATE (
                SUM ( 'Table'[Value] ),
                FILTER (
                    ALLSELECTED ( 'Table' ),
                    'Table'[Year] = _minyear
                        && 'Table'[Month] = SELECTEDVALUE ( 'Table'[Month] )
                )
            )
                - CALCULATE (
                    SUM ( 'Table'[Value] ),
                    FILTER (
                        ALLSELECTED ( 'Table' ),
                        'Table'[Year] = _maxyear
                            && 'Table'[Month] = SELECTEDVALUE ( 'Table'[Month] )
                    )
                ),
            CALCULATE (
                SUM ( 'Table'[Value] ),
                FILTER (
                    ALLSELECTED ( 'Table' ),
                    'Table'[Year] = _minyear
                        && 'Table'[Month] = SELECTEDVALUE ( 'Table'[Month] )
                )
            )
        )
    )

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[Dvaudreuil]

Thank you for this Kelly!

[v-kelly-msft]

Hi  @Dvaudreuil ,

Glad to help.😊

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[Dvaudreuil]

Thank you amitchandak!  I will give this a try. 

[amitchandak]

@Dvaudreuil , You have to create a measure. and add it

with help from date table 

This Year = CALCULATE(sum("order"[Qty]),filter(ALL("Date"),"Date"[Year]=max("Date"[Year])))
Last Year = CALCULATE(sum("order"[Qty]),filter(ALL("Date"),"Date"[Year]=max("Date"[Year])-1))

diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])

other option

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD("Date"[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd("Date"[Date],-1,Year),"12/31"))