Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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
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
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.
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
No one?
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] )
Best Regards!
Dale
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
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
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
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
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