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

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