I want to project my Stocks Units available to future dates

[Anonymous]

I have Sales Inventory Available for each product for the Last Day (eg 31/07/2021) Like shown in Table below
Date	Product	Units Available
31/07/2021	A	10
31/07/2021	B	20
31/07/2021	C	15
31/07/2021	D	25
31/07/2021	E	30
31/07/2021	F	40
I have calculated average Daily Sales for all the Above products like shown in Table below
Product	Avg Daily Sales
A	1
B	2
C	1
D	2
E	3
F	4
I have another table which contains information about incoming units for that product like shown Below
Date	Product	Incoming Units
7/8/2021	A	10
11/8/2021	B	20
12/8/2021	C	35
15/8/2021	D	20
20/8/2021	E	30
25/8/2021	F	20
Desired Output:
1/8/2021	A	Units Available on Previous Date (= 10 From Table 1) - Avg Daily Sales (=1 From Table 2) + Incoming Stock (=0 Table 3)
1/8/2021	B	Units Available on Previous Date (=20 From Table 1) - Avg Daily Sales (=2 From Table 2) + Incoming Stock (=0 Table 3)
1/8/2021	C	Units Available on Previous Date (=15 From Table 1) - Avg Daily Sales (=1 From Table 2) + Incoming Stock (=0 Table 3)
1/8/2021	D	Units Available on Previous Date (=25 From Table 1) - Avg Daily Sales (=2 From Table 2) + Incoming Stock (=0Table 3)
1/8/2021	E	Units Available on Previous Date (=30 From Table 1) - Avg Daily Sales ( = 3 From Table 2) + Incoming Stock (=0 Table 3)
1/8/2021	F	Units Available on Previous Date (= 40 From Table 1) - Avg Daily Sales (=4 From Table 2) + Incoming Stock (=0 Table 3)
2/8/2021	A	Units Available on Previous Date (=9 on 1/8/2021) - Avg Daily Sales ( =2 From Table 2) + Incoming Stock (=0 Table 3)
2/8/2021	B	and so on…..
2/8/2021	C
2/8/2021	D
2/8/2021	E
2/8/2021	F
It is possible to achieve something like this in Power BI

[v-jingzhang]

Hi @Anonymous 

In the desired result table, do we need to show the projected units available for every product on every day (maybe from 1/8/2021 to 31/8/2021)? Is there an ending date?

Regards,
Community Support Team _ Jing

[Anonymous]

Hi,

Yes we need to show units available for every product for the next 2 months. (Products will be selected using a slicer which would be single select)

Thanks

[v-jingzhang · Accepted solution]

Hi @Anonymous 

I created two calculated tables to achieve the result. You could download the attached pbix at bottom for details. Here are some main steps:

1. Create a calculated table 'FutureDates' which have all dates in the next two months. 

FutureDates = CALENDAR(DATE(2021,8,1),DATE(2021,9,30))

2. Create a calculated table 'Projected Table' to combine future dates and products first. 

Projected Table = SUMMARIZECOLUMNS('FutureDates'[Date],Inventory[Product])

3. Create a calculated column in 'Projected Table' to get the projected stock for every day.

Projected Stock = 
VAR _originalStock = MAXX(FILTER(Inventory,Inventory[Product]='Projected Table'[Product]),Inventory[Units Available])
VAR _incomingUnits = SUMX(FILTER(IncomingUnits,IncomingUnits[Product]='Projected Table'[Product] && IncomingUnits[Date]<='Projected Table'[Date]),IncomingUnits[Incoming Units])
VAR _averageDailySales = MAXX(FILTER(DailySales,DailySales[Product]='Projected Table'[Product]),DailySales[Avg Daily Sales])
VAR _totalSales = DATEDIFF(MAX(Inventory[Date]),'Projected Table'[Date],DAY) * _averageDailySales
RETURN
_originalStock + _incomingUnits - _totalSales

Note that I didn't create any relationships between tables. 

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[Anonymous]

Hi @v-jingzhang,

This is what exactly I was looking for. Perfect Solution.

Thank you so much. Much Appreciate

Regards,

Vrushab Jain

[v-jingzhang]

Hi @Anonymous 

Glad it helps!

BTW, I found an earlier post of yours. Its expected result seems to be similar to this one, do you need further help on that one? If no need, you can post a reply there and mark as solution to close it. Thanks.

Regards,
Jing

[Ashish_Mathur]

Hi,

How often is the first table updated?  When that table is updated, does data for 31/7 remain?  When you get inventory data for 31/8 in table1, do you have to take the same sales for September from table2 - shouldn't there be dates in table2 as well?

Please share a representative dataset (rather than a mere theoretical one) and on that dataset, show the expected result.

[lbendlin]

Wouldn't you want to compute Average Daily Sales from your historical data?