Forum Discussion
JajatiDev
Helper II
1 year agoYoY Growth Card
Hello, I have created the measure to calculate yoy growth. VAR _CY = [Total Order Volume]
VAR _LY =
CALCULATE(
[Total Order Volume],
DATESBETWEEN(
Calendar_Tabl...
- 1 year ago
Thank you for all the valuable suggestions.
I was able to work it out.
Included the solution below for everyone's reference.
Also, sharing the screenshot of the card with the final outputYoY_OrderFlow% = VAR _currentDate = TODAY() VAR _currentYear = IF( MONTH(_currentDate) >= 4, YEAR(_currentDate) + 1, YEAR( _currentDate)) VAR _currentFY = "FY-" & _currentYear RETURN CALCULATE( VAR _cy = [TotalOrderedQuantity] VAR _ly_startDate = EDATE( MIN( Calendar_Table[Date]), -12) VAR _ly_endDate = EOMONTH( MAX( OrderData[ReceiveDate] ), -12) VAR _ly = CALCULATE( [TotalOrderedQuantity], DATESBETWEEN( Calendar_Table[Date], _ly_startDate, _ly_endDate ), REMOVEFILTERS( Calendar_Table[FY_Year] ) ) VAR isValidForGrowth = NOT ISBLANK( _cy ) && NOT ISBLANK( _ly ) && _ly <> 0 VAR _growth = IF( isValidForGrowth, DIVIDE( _cy, _ly) - 1) RETURN _growth, FILTER( ALL( Calendar_Table[FY_Year] ), Calendar_Table[FY_Year] = _currentFY ) )
JajatiDev
Helper II
1 year agoThank you for all the valuable suggestions.
I was able to work it out.
Included the solution below for everyone's reference.
Also, sharing the screenshot of the card with the final output
YoY_OrderFlow% =
VAR _currentDate = TODAY()
VAR _currentYear =
IF( MONTH(_currentDate) >= 4, YEAR(_currentDate) + 1, YEAR( _currentDate))
VAR _currentFY = "FY-" & _currentYear
RETURN
CALCULATE(
VAR _cy = [TotalOrderedQuantity]
VAR _ly_startDate =
EDATE( MIN( Calendar_Table[Date]), -12)
VAR _ly_endDate =
EOMONTH( MAX( OrderData[ReceiveDate] ), -12)
VAR _ly =
CALCULATE(
[TotalOrderedQuantity],
DATESBETWEEN(
Calendar_Table[Date],
_ly_startDate,
_ly_endDate
),
REMOVEFILTERS( Calendar_Table[FY_Year] )
)
VAR isValidForGrowth = NOT ISBLANK( _cy ) && NOT ISBLANK( _ly ) && _ly <> 0
VAR _growth = IF( isValidForGrowth, DIVIDE( _cy, _ly) - 1)
RETURN
_growth,
FILTER(
ALL( Calendar_Table[FY_Year] ),
Calendar_Table[FY_Year] = _currentFY
)
)