In this blog post, I will give you a comprehensive overview on the Power BI calculation groups. I’ll share with you some of my tutorials around calculation groups and you’ll see how and when to use t...
Updated 5 years ago
Version 2.0
Blog Post
I'm trying to write a DAX statement that will return the YoY % Cng for a 3 Month, 6 Month, and 12 Month rolling averages. Below is the Dax statement that is giving me an error. Any help would be appreciated.
VAR ValueCurrentPeriod = VAR NumOfMonths = 12 VAR LastCurrentDate = ENDOFMONTH ( 'MasterDateTable'[Date] ) VAR Period = DATESBETWEEN ( 'MasterDateTable'[Date], STARTOFMONTH ( DATEADD ( LastCurrentDate, - NumOfMonths, MONTH ) ), LastCurrentDate ) VAR Result = CALCULATE ( AVERAGEX ( VALUES ( 'MasterDateTable'[Calendar YearMonthNumber] ), SELECTEDMEASURE () VAR ValuePreviousPeriod = CALCULATE ( SELECTEDMEASURE ()VAR NumOfMonths = 12 VAR LastCurrentDate = ENDOFMONTH ( 'MasterDateTable'[Date] ) VAR Period = DATESBETWEEN ( 'MasterDateTable'[Date], STARTOFMONTH ( DATEADD ( LastCurrentDate, - NumOfMonths, MONTH ) ), LastCurrentDate ) VAR Result = CALCULATE ( AVERAGEX ( VALUES ( 'MasterDateTable'[Calendar YearMonthNumber] ), SELECTEDMEASURE () ), Period ) RETURN Result, SAMEPERIODLASTYEAR ( MasterDateTable[Date] ) ) VAR ValueYoY = IF ( NOT ISBLANK ( ValueCurrentPeriod ) && NOT ISBLANK ( valuepreviousperiod ), ValueCurrentPeriod - Valuepreviousperiod ) VAR Result = DIVIDE ( ValueYoY, ValuePreviousPeriod ) RETURN Result