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.
Hi Team,
I am trying to create a end of week stock calculation that reset when my inventory goes negative similar to an if function in excel.
We start out with 2,000 inventory.
Example 1:
End of week stock for 2/22/2021
7,000 (2/15/2021 End of Week stock) + 20,000 - 30,0000 = -3,000. Since it's a negative I want it to start from 0.
End of Week stock for 3/1/2021
0 (2/22/2021 End of week stock) + 40,000 - 5,000 = 35,000
@Earthinfusion is this what you are looking for?
Check my latest blog post Improve UX: Show Year in Legend When Using Time Intelligence Measures | PeryTUS IT Solutions I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@parry2k It's close but not exactly what I am looking for. You have the cumulative stock measurement right, but the Stock columns have an issues. I notice that the stock columns doesn't add in previous week inventory.
Example: March 20, 2021: Even though Supply - Demand = -1000, it's suppose to be 18000 (Week March 19,2021)+ Supply - Demand = 17000.
@Earthinfusion your explanation is all over the place and very confusing. As per details, seems like you wanted to reset the stock once it is negative, you need to put together data in an excel sheet and explain the logic otherwise there it is very hard to follow what you are looking for. you can share the file using one drive or google drive.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@parry2k Apologies for not having all my thoughts together. I have made a google document to show the logic which I prefer. Thank you for helping me with this.
Here is the link:
https://drive.google.com/file/d/1IzxM8cWrRuZzuTfGpBpwqh_I4XSFRBfw/view?usp=sharing
Please let me know if you have any questions.
Hi @Earthinfusion ,
Could you please provide the calculation logic for your measure? How to get the value 45000 for week 2/28/2021? By the way, is 9000 the original value? Whether it need to add 9000 for every week?
In addition, please review the solution in the following thread, hope it can help you.
Best Regards
Hi @v-yiruan-msft,
Apologies, I realize my mistake on my end. I have corrected the post with my latest measurement. Unfortuantely the post you linked didn't have resetting my measurement to start from 0 when it's goes negative.
Here is the break down of the third week calcualtion:
Last week (2/22/2021) = -3,000 (Since it's negative, I want it to reset and start from 0)
Supply = 40,000
Demand = 5,000
This week inventory = 35,000.
Hi @Earthinfusion,
You can create two measures as below:
Culmulate =
CALCULATE (
(
2000 + SUM ( 'Sheet1 (2)'[Supply] )
- SUM ( 'Sheet1 (2)'[Demand] )
),
FILTER (
ALLSELECTED ( 'Sheet1 (2)'[Week] ),
'Sheet1 (2)'[Week] <= SELECTEDVALUE ( 'Sheet1 (2)'[Week] )
)
)
Measure =
VAR _seldate =
SELECTEDVALUE ( 'Sheet1 (2)'[Week] )
VAR _ndate =
CALCULATE (
MAX ( 'Sheet1 (2)'[Week] ),
FILTER (
ALLSELECTED ( 'Sheet1 (2)' ),
[Culmulate] < 0
&& 'Sheet1 (2)'[Week] < _seldate
)
)
RETURN
IF (
[Culmulate] < 0,
0,
IF (
_seldate > _ndate&&NOT(ISBLANK(_ndate)),
CALCULATE (
( SUM ( 'Sheet1 (2)'[Supply] ) - SUM ( 'Sheet1 (2)'[Demand] ) ),
FILTER (
ALLSELECTED ( 'Sheet1 (2)'[Week] ),
'Sheet1 (2)'[Week] <= _seldate
&& 'Sheet1 (2)'[Week] > _ndate
)
),
[Culmulate]
)
)
Best Regards
Hi @v-yiruan-msft thanks for the formula and your time. Unfortunately it's close but did not meet all the requirements. Please see below.
If you change the supply of 3/07/2021 to 21,000 for supply. You will get a negative cumulative for 3/14/2021, since the cumulative doesn't reset when it's goes negative, it's build on it self. This in term made it so even though 3/14/2021 is a positive measure, it's will return 0:
Last week (3/14/2021)= 6,000
Supply = 30,000
Demand = 35,000
This week (3/21/2021) = 1,000
Instead, the measurement turn out to be 0.
The correct answer would have been 1,000.
Please let me know if this make sense. Thank you!
Hi,
Please share the link from where i can download your PBI file.
Hi @Ashish_Mathur ,
Do you have the instruction on how to get it link here? I am attempting but seem to be failing a bit short.
@Ashish_Mathur
Here is the google drive link: https://drive.google.com/file/d/1IzxM8cWrRuZzuTfGpBpwqh_I4XSFRBfw/view?usp=sharing
If you need the detail of the project and sample.
Hi,
I tried but could not solve it. Sorry.
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 |
---|---|
105 | |
96 | |
79 | |
66 | |
62 |
User | Count |
---|---|
145 | |
113 | |
105 | |
85 | |
65 |