Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Calculating usage based on a field not on data parameters

Hello I am working on an inventory report

 

We need to calculate usage based on LEAD TIME instead of date filters (Which we are currently using, varies between weeks days years etc)

 

The measure we are using for USAGE is:

Usage = SUMX ( TABLE, -1 * TABLE[QTY] )
Note: the quantity comes out in negatives so we have to turn it into a positive
 
The issue is that it calculates based on whatever date parameters we select. We need to implement the below lead time instead of whatever date(s) we select. How would I go about that. The LEAD time varies between items. Lead time is a column within our dataset btw

 

Item 1000

Lead time 35

Item 1001

Lead time 40.

 

Also how would i do so without having the date parameters affect the new measure.

Need Help

2 Replies

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

    Anonymous can you post sample data with the expected output you are trying to achieve, unfortunately, I'm not able to follow your question.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hello

       

      What i am trying to do is calculate usage based on lead time (which is a fixed number in days) and not the date range. The report I have calculates usage based on whatever date parameter they set and it could vary from 2 weeks, 90 days, 8 weeks etc.

       

      The measure I use to calculate usage is:

      Usage = SUMX ( TABLE, -1 * TABLE[QTY] )

       

      I have another measure I use to calculate lead time:

      ItemLeadTime =
      CALCULATE(
          SUMX(TABLE,
              TABLE[LEADTIME]),
                  FILTER(TABLE,
                     TABLE[INVENTDIMID] = "00000001_060" ))
       
      Lead time is simply the time it takes to get the ordered goods from the vendor to the shelf ready for picking. So it can be 35, 60, 90 days etc
       
      How can i calculate usage by lead time and not by the date filter.
       
       
      Do you still need to see my sample data?