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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |