Measure for YoY YTD

[egrospe17]

Hello DAX Gods,

I have this table in one of my PowerBI reports that shows YoY % Revenue growth. For the most part, this looks right with the execption of the current year because we are only in September. How can I show the YoY % to only calculate vs YTD of previous year?

I'm using the following DAX formula for the Rev YoY Growth %:

Rev YoY Growth % = 
VAR _currentyear_rev = [Total Revenue]
VAR _currentyear = MAX('400_Revenue'[Year])
VAR _previouseyear_rev = CALCULATE([Total Revenue],'400_Revenue'[Year] = _currentyear - 1)
VAR _YoYGrowth = 
    DIVIDE(
            _currentyear_rev - _previouseyear_rev, _previouseyear_rev
    )
RETURN
    IF(
        HASONEVALUE('400_Revenue'[Year]),
        IF(
            NOT ISBLANK(_currentyear_rev) && NOT ISBLANK(_previouseyear_rev),
            _YoYGrowth
        )
    )

[Anonymous · Accepted solution]

Hi @egrospe17 

Based on your needs, I have created the following table.

First you can use the following Measure to get the total revenue every year.

Total Revenue = 
VAR _year = [Year]
RETURN
SUMX(FILTER(ALL('Table'),'Table'[Year]=_year),'Table'[Revenue])

Then you can use the following Measure to get the result you want:

YoY % Revenue growth = 
VAR _year = [Year]
VAR _sameperiodtotal = CALCULATE ( [Total Revenue], DATESYTD ( SAMEPERIODLASTYEAR ( 'Table'[Date] ) ) )
VAR _maxmonth = MAXX (
            FILTER ( ALL ( 'Table' ), 'Table'[Year] = _year ),
            MONTH ( 'Table'[Date] )
        )
RETURN
    IF (
         _maxmonth< 12,
        DIVIDE (
            SUMX ( FILTER ( ALL ( 'Table' ), 'Table'[Year] = _year ), 'Table'[Revenue] ),
            SUMX (
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Year] = _year - 1
                        && MONTH ( 'Table'[Date] )
                            <= _maxmonth
                ),
                'Table'[Revenue]
            )
        )-1,
        DIVIDE (
            [Total Revenue]-_sameperiodtotal,
            _sameperiodtotal
        )
    )

Best Regards,

Jayleny

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

[SachinNandanwar]

VAR _previouseyear_rev = CALCULATE([Total Revenue],'400_Revenue'[Year] = _currentyear - 1)

Did you try using SAMEPERIODLASTYEAR ?

[egrospe17]

Hi SachinNandanwar,

How would I incorporate SAMEPERIODLASTYEAR to my existing formula? 

I tried following this thread but it didn't produced the result I needed. the YoY% just returned as null when I put it on a table.

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/YTD-and-YoY-for-Measure/m-p/3536287#...

[SachinNandanwar]

Hi @egrospe17 : So basically you need to compare Jan-Sep 2023 with Jan-Sep 2024 for calculating YoY%, right ?

For that you can use a combination of DATESYTD and SAMEPERIODLASTYEAR.

Something like 

CALCULATE([Total Revenue], DATESYTD(SAMEPERIODLASTYEAR('400_Revenue'[Date])))