Cálculos dinámicos de CAGR

[charleshale]

Digamos que quiero devolver la última fecha independientemente del contenido de fecha de un objeto visual. Simple:

Var _MaxDate = Max (/datecolumn being measured/)
Var _Lastdate = CALCULATE( [_MaxDate] , all (DimDate) )   //dimdate is my generalized date table
RETURN
_LastDate

Resultados:

Digamos sin embargo que quiero devolver una medida que a su vez utiliza esta fecha para su cálculo, como los ingresos para el trimestre que termina 9/30 (e idealmente dinámico para que si el objeto visual es un año, agarra el año terminando -- aunque esa parte que sé usar hasonevalue). De todos modos, ¿por qué estoy luchando para fijar un valor en una fecha determinada si puedo obtener la fecha fijada?

El contexto es que estoy tratando de construir una función CAGR dinámica que muestra CAGR basado en el valor final más reciente que coincide con el período visual. Así que si es un gráfico trimestral y la última fecha es 9/30/2020, ese sería el valor final para todos los cálculos, con el valor inicial siendo el contexto de fecha

Las medidas funcionan en lo siguiente excepto para hacerla variable para trimestre / año etc (aunque estoy planeando usar hasonevalue para arreglar eso) y fijar el _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

[Syndicate_Admin]

No @charleshale,

¿Resolviste el problema? Vi tu puesto en la sección sin respuesta. Sólo por curiosidad, ¿estás viendo el siguiente tipo de comportamiento por las medidas?

Tenga en cuenta que acabo de usar la misma tabla de calendario para fijar la última fecha usando una segmentación de datos. Pero en un escenario real, la última fecha que se fija se recuperará de otra tabla de transacciones. Usé una cortadora sólo para la ilustración.

saludos

Sreenath

[Syndicate_Admin]

Bueno, es complicado. Hay un error en PowerBI que ha estado jugando con la fecha del tipo de datos donde muestra los datos, pero se trata como datetime de tal manera que necesito ajustar la función de fecha en INT(). Este ha sido el problema subyacente. @marcorusso lo ha informado.

En este ejemplo específico, #1, lo siguiente es una mejor construcción hasonevalue() para identificar el período de una fecha-- mucho más simple. Lo recomiendo.

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

Y aquí está el super-duper "flexi-CAGR" me decidí en :

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

Parece complicado, pero finge que fGL es cualquier viejo GL. Básicamente hay un conjunto de fechas variables para la N en el CAGR, y el concepto de un YearQuarterNumber para ayudar a que los recuentos más fácil ----- y definitivamente mucho más fácil sin un error de fecha y hora!