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.
Dear all, I have a table below:
Date | CusID | ProID | Stock |
13/10/2023 | 1 | 1 | 10 |
13/10/2023 | 1 | 2 | 5 |
13/10/2023 | 1 | 3 | 7 |
13/10/2023 | 2 | 1 | 13 |
13/10/2023 | 2 | 2 | 8 |
13/10/2023 | 2 | 3 | 15 |
19/10/2023 | 1 | 1 | 15 |
19/10/2023 | 1 | 2 | 7 |
19/10/2023 | 1 | 3 | 9 |
19/10/2023 | 2 | 1 | 10 |
19/10/2023 | 2 | 2 | 10 |
19/10/2023 | 2 | 3 | 9 |
24/10/2023 | 1 | 1 | 20 |
24/10/2023 | 1 | 2 | 15 |
24/10/2023 | 1 | 3 | 15 |
24/10/2023 | 2 | 1 | 5 |
24/10/2023 | 2 | 2 | 3 |
24/10/2023 | 2 | 3 | 3 |
31/10/2023 | 1 | 1 | 30 |
31/10/2023 | 1 | 2 | 30 |
31/10/2023 | 1 | 3 | 30 |
31/10/2023 | 2 | 1 | 20 |
31/10/2023 | 2 | 2 | 25 |
31/10/2023 | 2 | 3 | 25 |
Then I want to calculate remain stock by time, Customer and Product like below:
It means from 19/10/2023, with customer 1 and product 1, the remaining stock will calculated by stock on 19/10/2023 - stock on 13/10/2023 --> results is 5
Please help me explain for this case
Solved! Go to Solution.
Hi @neonguyen1803
Please refer to attached sample file with proposed calculated column solution
Remain Stock =
VAR CurrentStock = 'Table'[Stock]
VAR Change =
SUMX (
FILTER (
CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[CusID], 'Table'[ProID] ) ),
'Table'[Date] < EARLIER ( 'Table'[Date] )
),
'Table'[Stock]
)
RETURN
CurrentStock - Change
@neonguyen1803
Yes sure, here you go
Remain Stock =
VAR CurrentStock = 'Table'[Stock]
VAR PreviousStock =
SUMX (
TOPN (
1,
FILTER (
CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[CusID], 'Table'[ProID] ) ),
'Table'[Date] < EARLIER ( 'Table'[Date] )
),
'Table'[Date]
),
'Table'[Stock]
)
RETURN
CurrentStock - PreviousStock
Hi @neonguyen1803
Please refer to attached sample file with proposed calculated column solution
Remain Stock =
VAR CurrentStock = 'Table'[Stock]
VAR Change =
SUMX (
FILTER (
CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[CusID], 'Table'[ProID] ) ),
'Table'[Date] < EARLIER ( 'Table'[Date] )
),
'Table'[Stock]
)
RETURN
CurrentStock - Change
It's really great. One more question, I just want subtract with nearlest day, example: 19/10/2023 will subtract 13/10/2023, 24/10/2023 subtract 19/10/2023. Please help me explain for extended question
@neonguyen1803
Yes sure, here you go
Remain Stock =
VAR CurrentStock = 'Table'[Stock]
VAR PreviousStock =
SUMX (
TOPN (
1,
FILTER (
CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[CusID], 'Table'[ProID] ) ),
'Table'[Date] < EARLIER ( 'Table'[Date] )
),
'Table'[Date]
),
'Table'[Stock]
)
RETURN
CurrentStock - PreviousStock
Thank you so much
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 |
---|---|
56 | |
27 | |
24 | |
14 | |
9 |
User | Count |
---|---|
77 | |
61 | |
47 | |
17 | |
12 |