Forum Discussion

nyllshanson's avatar
nyllshanson
Frequent Visitor
7 years ago
Solved

Calculate Days Between Last Change in Expression

I have a DAX formula that calculates Average Selling Price (ASP) from transactional data (DollarAmount / Quanity).

 

The formula that I want to build using that formula as a base involves finding the number of days since the ASP value has changed. So if on 12/19/18 the ASP was $3.23 then I want to find how many days it has been at that value. So if on 12/2/18 the ASP was $2.22 and it changed to $3.23 on 12/3/18 and didn't change then the formula would come up with 16 days.

 

I'm not sure if this is enough of an explanation, but here is what I have so far:

 

EVALUATE
SUMMARIZE(
FILTER('Time', NOT(ISBLANK([ASP]))),
'Time'[CalendarDate],
"ASP", [ASP],
"CurrDate", SELECTEDVALUE('Time'[CalendarDate]),
"CurrASP", [ASP],
"PrevDate", CALCULATE(
MAX('Time'[CalendarDate]),
FILTER(
ALL('Time'[CalendarDate]),
[ASP]<>CALCULATE([ASP], 'Time'[CalendarDate]=EARLIER('Time'[CalendarDate],2)) &&
'Time'[CalendarDate]<EARLIER('Time'[CalendarDate],1)
)
)
)