Forum Discussion

Bart_1989's avatar
Bart_1989
Frequent Visitor
9 years ago

Inventory age based on batch manufacturing date

Hi everyone,

 

I have a table with inventory transactions that I use to calculate movements as well as inventory levels per day.

 

Quantity:=SUM(InventTrans[Qty])

 

Stock Quantity:=CALCULATE(

IF(SUM(InventTrans[PhysicalQty])=0;BLANK();SUM(InventTrans[PhysicalQty]));

FILTER(ALL(DatePhysical[Date]);DatePhysical[Date] <= MAX(DatePhysical[Date])

)

)

 

My table also contains batch numbers with a manufacturing date. For all batch related inventory, I want to calculate the average age over all batches dynamically. I have tried countless functions and the closest I could get is the following. I did not even get far enough to move to averages (either using an average function or sum and divide by the distinctcount)

 

Batch Age:=CALCULATE(

IF([Stock Quantity]=0;BLANK();DATEDIFF(MAX([ManufacturingDate]);MAX(DatePhysical[Date]);DAY));

FILTER(ALL(DatePhysical[Date]);DatePhysical[Date] <= MAX(DatePhysical[Date])

)

)

 

This is giving me the following result:

 

Row LabelsBatch1Batch2Batch3Batch4Grand Total
01.06.201716   16
02.06.201717   17
03.06.201718   18
04.06.201719   19
05.06.201720   20
06.06.201721   21
07.06.201722   22
08.06.201723   23
09.06.2017240  0
10.06.2017251  1
11.06.2017262  2
12.06.2017 3  3
13.06.2017  0 0
14.06.2017  1 1
15.06.2017  2 2
16.06.2017  3 3
17.06.2017  4 4

 

What I want to achieve is that, for example on the 11th of June, I get an average of 14 days and for the 12th I get 3 days.

I suspect I need to do something with the MAX([ManufacturingDate]). In SQL I would try to use an over clause to determine the max per batch number.

 

What I also want to do is create a filter to group the inventory levels based on ages (0-20 days, 21-40 days, etc.). If I would then select 11.06.2017 in my report, the quantity for Batch 1 would be in the 21-40 days group and the quantity for Batch 2 in the 0-20 days group.

 

Can anyone help me out?

 

Regards,

Bart

Need Help

10 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    9 years ago

    Hi Bart_1989,

     

    Could you please post a sample here? 

     

    1. Did you mean (26 + 2)/(2 batches) = 14 of 11.06 and 3/(1 batch) = 3 of 12.06 ? 

    2. What are batches? Measure, column or calculated column?

     

    Best Regards!

    Dale

    • Bart_1989's avatar
      Bart_1989
      Frequent Visitor
      9 years ago

      Hi v-jiascu-msft,

       

      You are right. I would like it to show me (26+2)/2 = 14 on the 11th and 3 on the 12th.

      A batch is just a number available in my source data. It's currently just a normal column.

       

      I'm not sure what you require in the example. If the above information is not enough, can you please let me know?

      Thank you!

       

      Regards,

      Bart

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    9 years ago

    Hi Bart_1989,

     

    You mean "the following result" is your source table. If so, you could try the formula below to get the average. (pay attention to the blue part.)

     

    average =
 ( [Batch1] + [Batch2]
    + [Batch3]
    + [Batch4] )
    / CALCULATE (
        COUNT ( Table1[Batch1] ) + COUNT ( Table1[Batch2] )
            + COUNT ( Table1[Batch3] )
            + COUNT ( Table1[Batch4] )
    )

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    2. We should create a table of levels, then we can add new levels easily. 

     

    Batch1Group =
CALCULATE (
    VALUES ( Table2[ID] ),
    FILTER (
        'Table2',
        'Table2'[Start] <= MIN ( 'Table1'[Batch1] )
            && 'Table2'[End] >= MIN ( 'Table1'[Batch1] )
    )
)
    Batch2Group =
CALCULATE (
    VALUES ( Table2[ID] ),
    FILTER (
        'Table2',
        'Table2'[Start] <= MIN ( 'Table1'[Batch2] )
            && 'Table2'[End] >= MIN ( 'Table1'[Batch2] )
    )
)

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards!

    Dale

    • Bart_1989's avatar
      Bart_1989
      Frequent Visitor
      9 years ago

      Hi v-jiascu-msft,

       

      You are right. It was a result of the query mentioned before that but at the same time input for the next query.

      I will try your solution today and let you know!

       

      Regards,

      Bart

      • Bart_1989's avatar
        Bart_1989
        Frequent Visitor
        9 years ago

        Hi v-jiascu-msft,

         

        I should give you some more background information I suppose.

        My table is actually only the following records (simplified, I have way more batch numbers, probably over 100.000+). The date is the transaction date in an inventory transaction table. The ManufacturingDate is coming from a different table and has a one to one relationship to the batch number.

         

        InventBatchIDDateManufacturingDatePhysicalQty
        SAS613971127.02.201727.02.20172'843'449
        SAS613971101.03.201727.02.2017333'360
        SAS613971115.03.201727.02.2017-3'176'809
        SAS614091401.03.201701.03.2017619'549
        SAS614091402.03.201701.03.2017311'136
        SAS614091406.03.201701.03.2017110'867
        SAS614091423.03.201701.03.2017-1'041'552
        SAS614224606.03.201706.03.2017310'191
        SAS614224613.03.201706.03.2017295'172
        SAS614224630.03.201706.03.2017-605'363
        SAS614340727.03.201727.03.20173'100'214
        SAS614340730.03.201727.03.2017351'248
        SAS614340706.04.201727.03.2017-1'856'143
        SAS614340719.04.201727.03.2017-1'595'319
        SAS614468017.04.201717.04.20171'861'368
        SAS614468018.04.201717.04.2017307'577
        SAS614468019.04.201717.04.2017310'747
        SAS614468021.04.201717.04.201733'336
        SAS614468027.04.201717.04.2017-2'513'028
        SAS614519804.05.201704.05.2017311'136
        SAS614519805.05.201704.05.2017933'408
        SAS614519808.05.201704.05.2017283'465
        SAS614519809.05.201704.05.2017-1'528'009
        SAS614620615.05.201715.05.2017933'408
        SAS614620616.05.201715.05.20171'244'266
        SAS614620617.05.201715.05.2017914'295
        SAS614620622.05.201715.05.201793'006
        SAS614620625.05.201715.05.2017-3'184'975
        SAS614620716.05.201716.05.2017310'858
        SAS614620717.05.201716.05.2017622'272
        SAS614620718.05.201716.05.2017933'074
        SAS614620723.05.201716.05.2017255'576
        SAS614620712.06.201716.05.2017-2'121'780
        SAS614718509.06.201709.06.2017353'861
        SAS614718512.06.201709.06.2017615'298
        SAS614718513.06.201709.06.2017-969'159
        SAS614809913.06.201713.06.2017355'584
        SAS614809914.06.201713.06.2017353'639
        SAS614809915.06.201713.06.2017349'917
        SAS614809928.06.201713.06.2017-1'059'140
        SAS614809929.06.201713.06.2017441'591
        SAS614965721.07.201721.07.2017702'498
        SAS614965724.07.201721.07.20171'356'299
        SAS614965725.07.201721.07.2017-1'058'082
        Grand Total  1'442'306

         

        Based on this table and a date table containing all dates, I am able to calculate daily stock levels using the below query I mentioned earlier:

         

        Stock Quantity:=CALCULATE(

        IF(SUM(InventTrans[PhysicalQty])=0;BLANK();SUM(InventTrans[PhysicalQty]));

        FILTER(ALL(DatePhysical[Date]);DatePhysical[Date] <= MAX(DatePhysical[Date])

        )

        )

         

        I would then like to calculate an average batch age per day and also put the already calculated stock quantities into age groups. Because the table I posted earlier is a result from a measure and not an actual table, I'm not sure how to implement your proposed solution.

         

        I tried calculating the batch using the same sort of query as for the stock quantity:

         

        Batch Age:=CALCULATE(

        IF([Stock Quantity]=0;BLANK();DATEDIFF(MAX([ManufacturingDate]);MAX(DatePhysical[Date]);DAY));

        FILTER(ALL(DatePhysical[Date]);DatePhysical[Date] <= MAX(DatePhysical[Date])

        )

        )

         

        The issue with this query lies (I think) in the MAX([ManufacturingDate]) because it's taking the highest one per date. I would need the highest one per date and per batch and sum those up.

         

        Regards,

        Bart