Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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 |
Solved! Go to Solution.
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.
2. Create a calculated table 'Projected Table' to combine future dates and products first.
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.
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
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
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.
2. Create a calculated table 'Projected Table' to combine future dates and products first.
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.
Hi @v-jingzhang,
This is what exactly I was looking for. Perfect Solution.
Thank you so much. Much Appreciate
Regards,
Vrushab Jain
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
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.
Wouldn't you want to compute Average Daily Sales from your historical data?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
76 | |
74 | |
55 | |
45 |
User | Count |
---|---|
117 | |
106 | |
77 | |
66 | |
65 |