Calculate Date when Running Total hits target

[Anonymous]

Hi community,

I'm pretty new to Power BI and Dax and pretty frustrated because I'm not able so solve this problem even after hours of googeling (I found forum posts (e.g. here and here ) which were related to my problem but I wasn't able to apply the solutions).  I would really appreciate your help.

Scenario: 
I want to predict the development of my inventory. I know how many items are in stock at the moment and I know when a specific amount of a specific product will be withdrawn. 

My Goal:

I want to know on which day the inventory for a given product will drop below zero. Therefore I want to create a calculated column (or a measure, you tell me)  for the product dimension table where this date is displayed.

Test Data:

I created a test pbix (Link) to showcase my problem.

Relationships:

dim Date 1 -- * Fact Transactions * -- 1 dim Products

I created a running total/cummulative total measure using the standard formula:

Cumulative Quantity =

CALCULATE (

  SUM ( 'Fact Future Transactions'[Quantity]);

  FILTER (

    ALL ( 'Dim Date'[Date] );

    'Dim Date'[Date] <= MAX ( 'Dim Date'[Date] )

)

)

The Transactions Table looks like this. The positive numbers represent the current stock (today is the 3rd of June) while the negative numbers below represent future withdrawals.

Date	Quantity	Product
03.06.2020	10	A
03.06.2020	10	B
04.06.2020	-2	A
05.06.2020	-2	B
06.06.2020	-2	A
07.06.2020	-3	B
08.06.2020	-3	A
09.06.2020	-3	B
10.06.2020	-3	A
11.06.2020	-4	B
12.06.2020	-4	A

And the result I want to see in my Dim Products Table looks like this:

Product ID	Runs out on
A	10.06.2020
B	11.06.2020

I appreciate the help. Thank you in advance.

Lucas

[parry2k · Accepted solution]

@Anonymous try following measure:

Stock Run Out Date = 
VAR __date = CALCULATETABLE( FILTER( ALL ( 'Dim Date'[Date] ), 'Dim Date'[Date] <= MAX ( 'Dim Date'[Date] ) ) ) 
VAR __data = 
SUMMARIZE ( 
    CROSSJOIN ( 
        VALUES ( 'Dim Products'[Product ID] ),
        __date 
    ),
    'Dim Products'[Product ID], 
    'Dim Date'[Date], 
    "__cumulative Quantity",  [Cumulative Quantity],
    "__cumulative Is Blank",  [Cumulative Quantity] ==BLANK() 
) 
RETURN

MINX ( 
    FILTER (
        __data,
        [__cumulative Quantity] <=0 && 
        NOT [__cumulative Is Blank]
    ), 
    [Date] 
)

And here is the output

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos 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.⚡

[Anonymous]

Thank you @parry2k . That measure did the trick. Allow me to ask a follow up question: What are the advantages of using a measure instead of calculated column here?

[parry2k]

@Anonymous great question. read this post

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos 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.⚡