Rate of change - DAX code

[leeham]

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
)

[leeham · Accepted solution]

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)

[tamerj1]

@leeham 

Are you sure there are no blanks?

[leeham]

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?

[tamerj1]

@leeham 

Ah! Just now I saw the code. You cannot just -1 from a month number. Suppose the month number is 1 then result would be 0 which results in an error. Are you trying to calculate the value for the previous month?

[leeham]

I am yes - more specifically X number of months ago. Sometimes it may only be 1, sometimes it may be 3 or more.
Looking to copy the measure solution to do a year version.

The date ranges from 31-Jan-19 to 30-Sep-22. Only the end of the month is captured and not inbetween days.