Forum Discussion
Rate of change - DAX code
Hi,
I feel like I am missing something and wondered if anyone could see the mistake and know the solution?
Date referenced in the code is a date (monthly) within a date table with a DD Month YYYY format.
When I use the code below by using DATE function, i get this error:
Error Message:
MdxScript(Model) (368, 21) Calculation error in measure 'Append1'[New rate Delta]: An argument of function 'DATE' has the wrong data type or the result is too large or too small.
If I try the parrellperiod function, i get this error:
Error Message:
MdxScript(Model) (367, 31) Calculation error in measure 'Append1'[New rate Delta]: A table of multiple values was supplied where a single value was expected.
New rate Delta =
var max_date = MAX(Dates[Date])
--var minus_1_dates = CALCULATE(PARALLELPERIOD(Dates[Date],-1,MONTH),Dates[Date]=MAX(Dates[Date]))
var minus_1_dates = DATE(YEAR(MAX(Dates[Date])),MONTH(MAX(Dates[Date]))-1, DAY(MAX(Dates[Date])))
var curr = CALCULATE(Append1[new_rate],Dates[Date]=max_date)
var prev =
CALCULATE(Append1[new_rate],Dates[Date]=minus_1_dates)
return
DIVIDE(curr-prev,
prev
)
Solution:
I was close with using parallelperiod.
When i used lastdate with it, it pulled through the one month i needed and the rest worked.
With it set to -1 it will pull the lastdate -1 month (as I specifcied month in the parallperiod).
This solution gives the freedom to set any number of days/months/years.
The solution will also allow for user input if you tweak the -1 to a variable that reads user input.New rate Delta = var minus_1_dates = CALCULATE(LASTDATE(PARALLELPERIOD(Dates[Date],-1,MONTH))) return CALCULATE(Append1[new_rate],Dates[Date]=minus_1_dates)
5 Replies
Solution:
I was close with using parallelperiod.
When i used lastdate with it, it pulled through the one month i needed and the rest worked.
With it set to -1 it will pull the lastdate -1 month (as I specifcied month in the parallperiod).
This solution gives the freedom to set any number of days/months/years.
The solution will also allow for user input if you tweak the -1 to a variable that reads user input.New rate Delta = var minus_1_dates = CALCULATE(LASTDATE(PARALLELPERIOD(Dates[Date],-1,MONTH))) return CALCULATE(Append1[new_rate],Dates[Date]=minus_1_dates)
Thanks for getting back to me.
EDIT: new rate doesn't have blanks. In the measure that creates new_rate, I had set blanks to 0. I'm guessing this means it is something else.
The date field will be full and complete...the new_rate measure may contain blanks.
Let's assume it does, what would you do to fix that?
