cancel
Showing results for
Did you mean:
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 Labels Batch1 Batch2 Batch3 Batch4 Grand Total 01.06.2017 16 16 02.06.2017 17 17 03.06.2017 18 18 04.06.2017 19 19 05.06.2017 20 20 06.06.2017 21 21 07.06.2017 22 22 08.06.2017 23 23 09.06.2017 24 0 0 10.06.2017 25 1 1 11.06.2017 26 2 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
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.
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

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

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

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

## Re: Inventory age based on batch manufacturing date

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

Frequent Visitor

## Re: Inventory age based on batch manufacturing date

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.

 InventBatchID Date ManufacturingDate PhysicalQty SAS6139711 27.02.2017 27.02.2017 2'843'449 SAS6139711 01.03.2017 27.02.2017 333'360 SAS6139711 15.03.2017 27.02.2017 -3'176'809 SAS6140914 01.03.2017 01.03.2017 619'549 SAS6140914 02.03.2017 01.03.2017 311'136 SAS6140914 06.03.2017 01.03.2017 110'867 SAS6140914 23.03.2017 01.03.2017 -1'041'552 SAS6142246 06.03.2017 06.03.2017 310'191 SAS6142246 13.03.2017 06.03.2017 295'172 SAS6142246 30.03.2017 06.03.2017 -605'363 SAS6143407 27.03.2017 27.03.2017 3'100'214 SAS6143407 30.03.2017 27.03.2017 351'248 SAS6143407 06.04.2017 27.03.2017 -1'856'143 SAS6143407 19.04.2017 27.03.2017 -1'595'319 SAS6144680 17.04.2017 17.04.2017 1'861'368 SAS6144680 18.04.2017 17.04.2017 307'577 SAS6144680 19.04.2017 17.04.2017 310'747 SAS6144680 21.04.2017 17.04.2017 33'336 SAS6144680 27.04.2017 17.04.2017 -2'513'028 SAS6145198 04.05.2017 04.05.2017 311'136 SAS6145198 05.05.2017 04.05.2017 933'408 SAS6145198 08.05.2017 04.05.2017 283'465 SAS6145198 09.05.2017 04.05.2017 -1'528'009 SAS6146206 15.05.2017 15.05.2017 933'408 SAS6146206 16.05.2017 15.05.2017 1'244'266 SAS6146206 17.05.2017 15.05.2017 914'295 SAS6146206 22.05.2017 15.05.2017 93'006 SAS6146206 25.05.2017 15.05.2017 -3'184'975 SAS6146207 16.05.2017 16.05.2017 310'858 SAS6146207 17.05.2017 16.05.2017 622'272 SAS6146207 18.05.2017 16.05.2017 933'074 SAS6146207 23.05.2017 16.05.2017 255'576 SAS6146207 12.06.2017 16.05.2017 -2'121'780 SAS6147185 09.06.2017 09.06.2017 353'861 SAS6147185 12.06.2017 09.06.2017 615'298 SAS6147185 13.06.2017 09.06.2017 -969'159 SAS6148099 13.06.2017 13.06.2017 355'584 SAS6148099 14.06.2017 13.06.2017 353'639 SAS6148099 15.06.2017 13.06.2017 349'917 SAS6148099 28.06.2017 13.06.2017 -1'059'140 SAS6148099 29.06.2017 13.06.2017 441'591 SAS6149657 21.07.2017 21.07.2017 702'498 SAS6149657 24.07.2017 21.07.2017 1'356'299 SAS6149657 25.07.2017 21.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

Frequent Visitor

## Re: Inventory age based on batch manufacturing date

No one?

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 (
FILTER (
CROSSJOIN ( 'DatePhysical', 'InventTrans' ),
'DatePhysical'[Dates] <= 'InventTrans'[Date]
&& 'DatePhysical'[Dates] >= 'InventTrans'[ManufacturingDate]
),
"days", DATEDIFF ( [ManufacturingDate], [Dates], DAY )
),
[InventBatchID],
[Dates],
[days]
)```

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

## Re: Inventory age based on batch manufacturing date

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

Frequent Visitor

## Re: Inventory age based on batch manufacturing date

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

Announcements

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

#### Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 238 members 2,899 guests
Recent signins: