topic Re: Measure for YoY YTD in DAX Commands and Tips

Measure for YoY YTD

egrospe17 — Fri, 27 Sep 2024 17:34:02 GMT

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

Re: Measure for YoY YTD

SachinNandanwar — Fri, 27 Sep 2024 17:57:38 GMT

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

Did you try using SAMEPERIODLASTYEAR ?

Re: Measure for YoY YTD

egrospe17 — Fri, 27 Sep 2024 19:12:41 GMT

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#M135886

Re: Measure for YoY YTD

SachinNandanwar — Fri, 27 Sep 2024 20:48:13 GMT

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

Re: Measure for YoY YTD

Anonymous — Fri, 04 Oct 2024 02:43:31 GMT

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.