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.
Hello Friends,
I had the following calculated tables from raw data.
I want to calculate
Sept' 21 . The closing stock for Sept'21 = Stock + Receipt of Sept - Consumption of Sept
For Oct'21.: Opening Stock of Oct'21= Closing stock of Sept'21,
Closing stock of Oct'21 = Opening stock of Oct'21 + Receipt of Oct'21 - Consumption of Oct'21.
For Nov'21 : Opening Stock of Nov'21= Closing stock of Oct'21,
Closing stock of NOv.'21 = Opening stock of Nov'21 + Receipt of Nov'21 - Consumption of Oct'21.
And so on....
Will be grateful to you for providing the solution.
Solved! Go to Solution.
Hi @Narayan ,
You can try this measure:
A =
VAR totalConsumption =
CALCULATE (
SUM ( 'Consumption'[Consumption] ),
FILTER (
ALLSELECTED ( 'Consumption'[Date].[Date] ),
[Date].[Date] <= MAX ( [Date].[Date] )
)
)
VAR totalstock =
SUM ( 'Stock'[Stock] )
VAR totalreceipt =
CALCULATE (
SUM ( 'Receipt'[Receipt] ),
FILTER (
ALLSELECTED ( 'Receipt'[Date].[Date] ),
[Date].[Date] <= MAX ( [Date].[Date] )
)
)
RETURN
totalstock + totalreceipt - totalConsumption
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Narayan ,
You can try this measure:
A =
VAR totalConsumption =
CALCULATE (
SUM ( 'Consumption'[Consumption] ),
FILTER (
ALLSELECTED ( 'Consumption'[Date].[Date] ),
[Date].[Date] <= MAX ( [Date].[Date] )
)
)
VAR totalstock =
SUM ( 'Stock'[Stock] )
VAR totalreceipt =
CALCULATE (
SUM ( 'Receipt'[Receipt] ),
FILTER (
ALLSELECTED ( 'Receipt'[Date].[Date] ),
[Date].[Date] <= MAX ( [Date].[Date] )
)
)
RETURN
totalstock + totalreceipt - totalConsumption
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Narayan , With help from date table, You have to follow a formula like
Inventory / OnHand
[Intial Inventory] + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))
where [Intial Inventory] is the first inventory. If this is the only inventory, then do the sum. else calculate based on min date
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |