cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Bart_1989 Frequent Visitor
Frequent Visitor

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

10 REPLIES 10
v-jiascu-msft Super Contributor
Super Contributor

Re: Inventory age based on batch manufacturing date

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Bart_1989 Frequent Visitor
Frequent Visitor

Re: Inventory age based on batch manufacturing date

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 Super Contributor
Super Contributor

Re: Inventory age based on batch manufacturing date

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] )
    )

Inventory age based on batch manufacturing date.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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] )
    )
)

Inventory age based on batch manufacturing date2.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Bart_1989 Frequent Visitor
Frequent Visitor

Re: Inventory age based on batch manufacturing date

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 Frequent Visitor
Frequent Visitor

Re: Inventory age based on batch manufacturing date

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

Bart_1989 Frequent Visitor
Frequent Visitor

Re: Inventory age based on batch manufacturing date

No one?

v-jiascu-msft Super Contributor
Super Contributor

Re: Inventory age based on batch manufacturing date

@Bart_1989

 

Hi Bart,

 

I tried to achieve this in your current report. Failed. How about creating a new table? I attach the PBIX here: https://1drv.ms/u/s!ArTqPk2pu-BkgQL-fm4sRKZ3W8Y8.

Notes: 1. The new table is: FinalResult and the new report is: FinalResult. Other tables and reports are only for your information.

2. The formula of creating a new table.

 

FinalResult =
SUMMARIZE (
    ADDCOLUMNS (
        FILTER (
            CROSSJOIN ( 'DatePhysical', 'InventTrans' ),
            'DatePhysical'[Dates] <= 'InventTrans'[Date]
                && 'DatePhysical'[Dates] >= 'InventTrans'[ManufacturingDate]
        ),
        "days", DATEDIFF ( [ManufacturingDate], [Dates], DAY )
    ),
    [InventBatchID],
    [Dates],
    [days]
)

Inventory age based on batch manufacturing date.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Bart_1989 Frequent Visitor
Frequent Visitor

Re: Inventory age based on batch manufacturing date

Hi @v-jiascu-msft,

 

Thanks for your answer and your effort!

I have meetings for the next upcoming days, but will surely try your solution as soon as I can. I will get back to you when I did!

 

Regards,

Bart

Bart_1989 Frequent Visitor
Frequent Visitor

Re: Inventory age based on batch manufacturing date

Hi @v-jiascu-msft,

 

Unfortunately, I can't get it to work and I decided to write a state table in SQL instead.

Cross joining DatePhysical and InventTrans is a way I don't really want to go in, as the InventTrans table can contain millions of records.

 

I want to thank you a lot for your help though.

 

Regards,

Bart

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 238 members 2,899 guests
Please welcome our newest community members: