Forum Discussion

mconte_89's avatar
mconte_89
Frequent Visitor
5 years ago

Reverse comulative stocking for Date and Product

Hi everybody,

i have a problem to resolve and i hope to help me to do.

 

I would like calculate a "dynamic stock" about components used in a production line starting from a recent date and going to farther starting from today. These dates could be variable by a sliding filter, so maybe, i should use a measure and not column date. We have used Earlier functions in our attempts, but our model counts more than 500.000 rows, so, calculating take very long time and it's static date on the entire dataset.

 

Example:

 

Component cuncatenated code will be decreased in every pass, starting from 1996 pieces in stock and arriving to 1626 pieces (for component and increasing date) 

 

Thank you so much to helping me

Need Help
Tutorial Requests

3 Replies

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi mconte_89 ,

     

    Based on your description, you want to calculate dynamic stock basd on the date in the slicer, am I right?

     

    You can create the following measure:

     

    Dynamic Stock =
MAX ( Table[Stock] )
    - CALCULATE (
        SUM ( Table[Sendable] ),
        FILTER ( ALL ( Table ), Table[Date] <= MAX ( Table[Date] ) )
    )

     

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

     

    Best Regards,

    Dedmon Dai

    • mconte_89's avatar
      mconte_89
      Frequent Visitor
      5 years ago

      Hi, 

      i tried to do this but Date field hasn't unique values, so, this dax formulas didn't work in my case. 

      Do you have another approch?

       

      Thank you so much

       

      Matteo

      • v-deddai1-msft's avatar
        v-deddai1-msft
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi mconte_89 ,

         

        What did you mean  but Date field hasn't unique values? You want to calculate dynamic stock for each component? iI suggest you create another date table for the slicer.

         

        You can try to use the following measure:

         

        Dynamic Stock =
MAX ( Table[Stock] )
    - CALCULATE (
        SUM ( Table[Sendable] ),
        FILTER ( ALLexcept ( Table,Table[Component] ), Table[Date] <= MAX ( Date[Date] ) )
    )

         

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

         

        Best Regards,

        Dedmon Dai