Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |