Forum Discussion
Dynamic CAGR Calcs
Let's say I want to return the last date independent of a visual's date content. Simple:
Var _MaxDate = Max (/datecolumn being measured/)
Var _Lastdate = CALCULATE( [_MaxDate] , all (DimDate) ) //dimdate is my generalized date table
RETURN
_LastDate
Results work:
Let's say however that I want to return a measure that in turn uses this date for its calculation, such as revenue for the quarter ending 9/30 (and ideally dynamic so that if the visual is a year, it grabs year ending -- although that part I know how to do using hasonevalue). Anyway, why I am struggling to fix a value at a given date if I can get the date pegged?
The context is that I am trying to build a dynamic CAGR function that shows CAGR based on most recent ending value matching the visual period. So if it's a quarterly chart and the last date is 9/30/2020, that would be the ending value for all calculations, with the starting value being the date context
The measures works in the following except for making it variable for quarter / year etc (although I'm planning on using hasonevalue to fix that) and fixing the _endingvalue
CAGR =
VAR startingyear = min(DimDate[Year]) //this returns year of current year
VAR latestyear = CALCULATE( max(DimDate[Year] ), all (DimDate) ) // this returns year as 2020 always
VAR _N = latestyear - startingyear + 1 // CAGR = EV/BV ^ (1/N) - 1
VAR _StartingValue = CALCULATE([^$Tx_PL+BS],DimDate[Year]= startingyear) //[^$Tx_PL+BS] is a measure of GL transactions
VAR _EndingValue = CALCULATE([^$Tx_PL+BS],DimDate[Year] = latestyear, ALL(DimDate)) struggling to come up with something that always pulls the last period of whatever periods the visual is showing
VAR cagr =
POWER(
DIVIDE(_EndingValue, _StartingValue )
, ( 1 / _N ) )
RETURN
cagr
2 Replies
Hi charleshale,
Did you solve the problem? I saw your post in the unanswered section. Just out of curiosity, are you looking at the following kind of behaviour by the measures?
Please note that I just used the same calendar table to fix the last date using a slicer. But in a real scenario, the last date that is fixed will be retrieved from another transaction table. I used a slicer only for illustration.
Regards,
Sreenath
Well, it's complicated. There's a bug in PowerBI that has been messing with data type date where it shows data but is treated as datetime such that I need to wrap date function in INT(). This has been the underlying problem. marcorusso has reported it.
On this specific example, #1, the following is a better hasonevalue() construction for identifying a date's period -- much simpler. I recommend it.
VAR PeriodType = SWITCH ( TRUE (), // Complete month selected HASONEVALUE ( 'DimDate'[YearMonthnumber] ), "month", // Complete quarter selected HASONEVALUE ( 'DimDate'[YearQuarter] ), "quarter", // Complete year selected HASONEVALUE ( 'DimDate'[Year] ), "year" ) RETURN SWITCH ( PeriodType, "year", CALCULATE ( [^$RevsK_USDE], PREVIOUSYEAR ( 'DimDate'[Date] ) ), "quarter", CALCULATE ( [^$RevsK_USDE], PREVIOUSQUARTER ( 'DimDate'[Date] ) ), "month", CALCULATE ( [^$RevsK_USDE], PREVIOUSMONTH ( 'DimDate'[Date] ) ) )And here it is the super-duper "flexi-CAGR" I settled on :
}CAGR = //NOTE - this uses all date equations at left for a CAGR that is dynamic based on period VAR PeriodType = SWITCH ( TRUE (), // Complete month selected HASONEVALUE ( 'DimDate'[YearMonthnumber] ), "month", // Complete quarter selected HASONEVALUE ( 'DimDate'[YearQuarter] ), "quarter", // Complete year selected HASONEVALUE ( 'DimDate'[Year] ), "year" ) VAR startingyear = min(DimDate[Year]) //this returns year of current year VAR MaxDate = CALCULATE( max(DimDate[Date] ), CALCULATETABLE(fGL, REMOVEFILTERS()) ) VAR LastYear2 = CALCULATE( max(DimDate[Year] ), CALCULATETABLE(fGL, REMOVEFILTERS()) ) // BETTER - RETURNS MAX DATE IN fGL whereas CALCULATE( max(DimDate[Year] ), all (DimDate) ) returns max dimdate. Also LASTNONBLANK(ALL(DimDate[Year]),DimDate[Year]) = much worse. almost never use because iterator VAR LastQTR2 = CALCULATE( max(DimDate[YearQuarterNumber] ), CALCULATETABLE(fGL, REMOVEFILTERS()) ) VAR LastMonth2 = CALCULATE( max(DimDate[YearMonthnumber] ), CALCULATETABLE(fGL, REMOVEFILTERS()) ) // VAR _EndingValueYear = CALCULATE ( [^$Tx_PL+BS], FILTER ( ALL ( DimDate ), DimDate[Year] = MAXX ( ALL ( 'DimDate' ), 'DimDate'[Year] ))) //less efficient than below VAR _Endingvalue = SWITCH ( PeriodType, // if wanted cal yr only "year", CALCULATE([^$Tx_PL+BS],DimDate[Year] = latestyear, ALL(DimDate)) , "year", CALCULATE([^$Tx_PL+BS],DimDate[YearQuarterNumber] >= LastQTR2-3 && DimDate[YearQuarterNumber] <= LastQTR2, ALL(DimDate)) , "quarter", CALCULATE([^$Tx_PL+BS],DimDate[YearQuarterNumber] = LastQTR2, ALL(DimDate)) , "month", CALCULATE([^$Tx_PL+BS],DimDate[YearMonthnumber] = LastMonth2, ALL(DimDate)) ) VAR _N = SWITCH ( PeriodType, // if wanted cal yr only "year", CALCULATE([^$Tx_PL+BS],DimDate[Year] = latestyear, ALL(DimDate)) , "year", LastYear2 - startingyear + 1 , "quarter", LastQTR2 - min (DimDate[YearQuarterNumber]) , "month", DATEDIFF( min(DimDate[Date]), MaxDate, MONTH)/12) VAR _StartingValue = CALCULATE([^$Tx_PL+BS],DimDate[Year]= startingyear) VAR cagr = POWER( DIVIDE(_Endingvalue, _StartingValue ) , ( 1 / max(1,_N) ) ) RETURN max(0,cagr - 1)It looks complicated but pretend fGL is any old GL. Basically there's a set of variable dates for the N in the CAGR, and the concept of a YearQuarterNumber to help make the counts easier ----- and definitely much easier without a date-datetime bug!
