cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
nyllshanson Visitor
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
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...


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


2 REPLIES 2
Super User
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...


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Community Support Team
Community Support Team

Re: Calculate Days Between Last Change in Expression

Hi @nyllshanson,

 

Could you please mark the proper answers as solutions?

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.