Forum Discussion

G_CR_23's avatar
G_CR_23
Icon for Helper I rankHelper I
1 year ago
Solved

HELP WITH A MEASURE: First Date Running Total is 0

Hi Everyone,
i am struggling with a dax measure.
I need to calculate the first date where the stock for a product is equal to 0.
I have 3 tables:
- Purchase Table
- Inbound Table
- Date Table

I have 3 measures:
Qty purchase = sum ('Purchase Table'[Qty purchase])
Qty Inbound = sum ('Inbound Table'[Quantity Inbound])
Actual Stock/**bleep** Total = Calculate (Qty Inbound - Qty Purchase, Filter ('Date Table', 'Date Table'[Date]<=Max('Date Table'[Date])

So i don't have a column with actual stock for date, but i calcute it with a measure. Inbound and Purchase are two different table and both are related with date table.
How do I calulate the first date whrre actual stock (that is a running total) is equal to 0)
Next there is an example with the desidered output.



Can Someone help me please?
I have no problem to calculate the first date of purchase or the firstdate of inbound, but i don't know how to calculate the first date where actual stock is 0 (Repeat, actual stock is a measure and obliviously the date need to be next to the first purchase date, that is a measure too).

Thank you so much!

G.





Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    G_CR_23 

    First, create the measures for cumulative quantities:
    Qty Purchase = SUM('Purchase Table'[Qty purchase])

    Qty Inbound = SUM('Inbound Table'[Quantity Inbound])

    **bleep** Qty Purchase = CALCULATE([Qty Purchase], FILTER(ALL('Date Table'), 'Date Table'[Date] <= MAX('Date Table'[Date])))

    **bleep** Qty Inbound = CALCULATE([Qty Inbound], FILTER(ALL('Date Table'), 'Date Table'[Date] <= MAX('Date Table'[Date])))


    Create the measure for the actual stock:
    Actual Stock = [**bleep** Qty Inbound] - [**bleep** Qty Purchase]


    Create the measure to find the first date where the actual stock is 0:
    First Date 0 Stock =
    CALCULATE(
    MIN('Date Table'[Date]),
    FILTER(
    ALL('Date Table'),
    [Actual Stock] = 0
    )
    )


    This measure will return the first date where the actual stock is 0 by iterating over all dates and checking the condition.

     

    If you need to ensure that the date is after the first purchase date, you can modify the measure to include that condition:
    First Date 0 Stock =
    VAR FirstPurchaseDate = CALCULATE(MIN('Date Table'[Date]), FILTER(ALL('Date Table'), [Qty Purchase] > 0))
    RETURN
    CALCULATE(
    MIN('Date Table'[Date]),
    FILTER(
    ALL('Date Table'),
    [Actual Stock] = 0 && 'Date Table'[Date] >= FirstPurchaseDate

     

    This measure first calculates the first purchase date and then finds the first date where the actual stock is 0 and the date is on or after the first purchase date.

2 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    1 year ago

    G_CR_23 

    First, create the measures for cumulative quantities:
    Qty Purchase = SUM('Purchase Table'[Qty purchase])

    Qty Inbound = SUM('Inbound Table'[Quantity Inbound])

    **bleep** Qty Purchase = CALCULATE([Qty Purchase], FILTER(ALL('Date Table'), 'Date Table'[Date] <= MAX('Date Table'[Date])))

    **bleep** Qty Inbound = CALCULATE([Qty Inbound], FILTER(ALL('Date Table'), 'Date Table'[Date] <= MAX('Date Table'[Date])))


    Create the measure for the actual stock:
    Actual Stock = [**bleep** Qty Inbound] - [**bleep** Qty Purchase]


    Create the measure to find the first date where the actual stock is 0:
    First Date 0 Stock =
    CALCULATE(
    MIN('Date Table'[Date]),
    FILTER(
    ALL('Date Table'),
    [Actual Stock] = 0
    )
    )


    This measure will return the first date where the actual stock is 0 by iterating over all dates and checking the condition.

     

    If you need to ensure that the date is after the first purchase date, you can modify the measure to include that condition:
    First Date 0 Stock =
    VAR FirstPurchaseDate = CALCULATE(MIN('Date Table'[Date]), FILTER(ALL('Date Table'), [Qty Purchase] > 0))
    RETURN
    CALCULATE(
    MIN('Date Table'[Date]),
    FILTER(
    ALL('Date Table'),
    [Actual Stock] = 0 && 'Date Table'[Date] >= FirstPurchaseDate

     

    This measure first calculates the first purchase date and then finds the first date where the actual stock is 0 and the date is on or after the first purchase date.

    • G_CR_23's avatar
      G_CR_23
      Icon for Helper I rankHelper I
      1 year ago

      Hi bhanu_gautam 
      ty so much!
      This is very interesting and it's very close to what i tried before.
      I had performance problem using filter (date table), the measure is very slow.
      So i had some changes and use removefilters (date table), but for qty purchased i filter on purchade table date.
      In this way i can perform the measure and it is less slow.

      The measure that you suggest is perfect and return the right result, however it performs well when i have some product filtered. When i have the complete list of product it performs very poorly.

      This surely depends from the model. Unfortunatly i can't modify it, so now i am trying some little changes to find a solution. 

      However, thank you so much, hope i'll find a solution!