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 am trying to reset negative rolling stock to 0 and recalculate the stock. In the screenshot below for example on date 2020/01/17 and 2020/01/21 it should be a 1.
This si the logic I have so far.
Not having your data model, it was tricky to come up with a DAX expression that included your [Measure 5] measure. So here are two versions that should help.
The first shows the approach you could take if Measure 5 was a column in your table (I called it Stock).
Rolling Since Last 1 =
VAR __thisdate =
SELECTEDVALUE ( Stock[Date] )
VAR __latest1value =
CALCULATE (
MAX ( Stock[Date] ),
ALL ( Stock[Date] ),
Stock[Measure 5] = 1,
Stock[Date] <= __thisdate
)
RETURN
CALCULATE (
SUM ( Stock[Measure 5] ),
ALL ( Stock[Date] ),
Stock[Date] <= __thisdate,
Stock[Date] >= __latest1value
)
Here is the version that uses [Measure 5] as a measure. You will have to test if it works for you.
Rolling Since Last 1 with Measure 5 =
VAR __thisdate =
SELECTEDVALUE ( Stock[Date] )
VAR __latest1value =
CALCULATE (
MAX ( Stock[Date] ),
ALL ( Stock[Date] ),
FILTER ( VALUES ( Stock[Date] ), [Measure 5] = 1 ),
Stock[Date] <= __thisdate
)
RETURN
CALCULATE (
SUMX ( VALUES ( Stock[Date] ), [Measure 5] ),
ALL ( Stock[Date] ),
Stock[Date] <= __thisdate,
Stock[Date] >= __latest1value
)
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi Pat,
Thank you for your repsonse. It is more like I need the rolling sum to be reset to 0 when it becomes negative. and not necessarily when measure is 1. Can this be generalised?
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 |
---|---|
47 | |
24 | |
20 | |
15 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
18 |