Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Bart_1989
Frequent Visitor

Inventory age based on batch manufacturing date

‎07-28-2017 06:49 AM

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

Labels:
Message 1 of 11
1,943 Views
0
10 REPLIES 10
v-jiascu-msft
Employee
Employee

‎07-31-2017 08:19 PM

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.
Message 4 of 11
1,892 Views
0
Bart_1989
Frequent Visitor
In response to v-jiascu-msft

‎07-31-2017 10:32 PM

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

Message 5 of 11
1,888 Views
0
Bart_1989
Frequent Visitor
In response to Bart_1989

‎07-31-2017 10:52 PM

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

Message 6 of 11
1,885 Views
0
Bart_1989
Frequent Visitor
In response to Bart_1989

‎08-06-2017 10:47 PM

No one?

Message 7 of 11
1,868 Views
0
v-jiascu-msft
Employee
Employee
In response to Bart_1989

‎08-07-2017 06:05 PM

@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.
Message 8 of 11
1,855 Views
Bart_1989
Frequent Visitor
In response to v-jiascu-msft

‎08-08-2017 10:44 PM

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

Message 9 of 11
1,851 Views
0
Bart_1989
Frequent Visitor
In response to Bart_1989

‎08-15-2017 12:36 AM

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

Message 10 of 11
1,840 Views
0
v-jiascu-msft
Employee
Employee
In response to Bart_1989

‎08-15-2017 01:15 AM

@Bart_1989

 

Hi Bart,

 

My pleasure. Cross joining with a filter may work. I tested it with a large table once. If you want to try, backup your work first.

 

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.
Message 11 of 11
916 Views
0
v-jiascu-msft
Employee
Employee

‎07-30-2017 11:49 PM

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.
Message 2 of 11
1,911 Views
0
Bart_1989
Frequent Visitor
In response to v-jiascu-msft

‎07-31-2017 06:04 AM

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

Message 3 of 11
1,905 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All