cancel
Showing results for
Did you mean:
Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Calculate Days Between Last Change in Expression

See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

Proud to be a Datanaut!

2 REPLIES 2
Super User

## Re: Calculate Days Between Last Change in Expression

See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

Proud to be a Datanaut!

Community Support Team

Hi @nyllshanson,