Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Earthinfusion
Frequent Visitor

Cumulation Calculation resetting when condition is meet. Like an if function in excel.

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 

 

powerbi.PNG

 
 
 
 
 
 
 
 
 
Goal: Like the end of week columns.
 
If function in excel: 
IF(PreviousEndofWeek+Current Week Supply - Current week Demand<=0,0, PreviousEndofWeek+Current Week Supply - Current week Demand)

Attempted:
Sumx, Calculation, as well as looping within PowerBi. Please let me know if you have any ideas on how to solve this. The biggest issue has been circular dependencies. To know the current month's inventory, you will need to adjust the previous week's inventory if it goes negative.

You can take a look at formula to see. 

 

13 REPLIES 13
parry2k
Super User
Super User

@Earthinfusion is this what you are looking for?

 

parry2k_0-1616112138393.png

 

 

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. 

v-yiruan-msft
Community Support
Community Support

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.

Cumulative sum of a measure

Best Regards

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

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

yingyinr_0-1615862009166.png

Best Regards

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

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur  Thank you for attempting! 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.