Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
)
)
)
Solved! Go to Solution.
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...
Hi @nyllshanson,
Could you please mark the proper answers as solutions?
Best Regards,
Dale
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...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
70 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |