Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculate Date when Running Total hits target

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.

 

DateQuantityProduct
03.06.202010A
03.06.202010B
04.06.2020-2A
05.06.2020-2B
06.06.2020-2A
07.06.2020-3B
08.06.2020-3A
09.06.2020-3B
10.06.2020-3A
11.06.2020-4B
12.06.2020-4A

 

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

 

Product IDRuns out on
A10.06.2020
B11.06.2020

 

I appreciate the help. Thank you in advance.

 

Lucas

 

Need Help
  • parry2k's avatar
    parry2k
    6 years ago

    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.⚡

3 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        parry2k
        Icon for Super User rankSuper User
        6 years ago

        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.⚡