Dynamic CAGR Calcs

[charleshale]

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

[Anonymous]

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

[charleshale]

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!