Forum Discussion
Stock reduction based on demand (2)
Dear All Mastah
Hii everyone I don't have any idea for this measure about inventory, please help. I want to get the results from reducing stock to demand. The point is that the stock calculation will decrease if there is demand on each date. please check table my expectations
I have 2 table fact. table demand & table stock.
Table Demand
| ArticleCode | Date Demand | Demand |
| A001 | 01-Mar | 200 |
| A001 | 02-Mar | 300 |
| A001 | 03-Mar | 200 |
| A001 | 04-Mar | 100 |
| A002 | 01-Mar | 200 |
| A002 | 02-Mar | 300 |
| A002 | 03-Mar | 4,400 |
| A002 | 04-Mar | 11 |
| A002 | 05-Mar | 131 |
| A002 | 06-Mar | 131 |
| A002 | 07-Mar | 313 |
| A002 | 08-Mar | 131 |
| A003 | 03-Mar | 200 |
| A003 | 04-Mar | 100 |
| A003 | 05-Mar | 200 |
| A003 | 06-Mar | 300 |
| A003 | 07-Mar | 1,000 |
| A003 | 08-Mar | 900 |
Table Stock
| ArticleCode | Stock |
| A001 | 10,000 |
| A002 | 9,000 |
| A003 | 5,000 |
My Expectation Matrix
Hi,
I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
WINDOW function (DAX) - DAX | Microsoft Learn
Balance: = VAR _demandcumulate = CALCULATE ( SUM ( Demand[Demand] ), WINDOW ( 1, ABS, 0, REL, ALL ( Date_demand[Date Demand] ), ORDERBY ( Date_demand[Date Demand], ASC ) ) ) RETURN IF ( HASONEVALUE ( Stock[ArticleCode] ) && HASONEVALUE ( Date_demand[Date Demand] ), MAXX ( Stock, Stock[Stock] ) - _demandcumulate )Stock: = VAR _demandcumulatepreviousdate = CALCULATE ( SUM ( Demand[Demand] ), WINDOW ( 1, ABS, -1, REL, ALL ( Date_demand[Date Demand] ), ORDERBY ( Date_demand[Date Demand], ASC ) ) ) RETURN IF ( HASONEVALUE ( Stock[ArticleCode] ) && HASONEVALUE ( Date_demand[Date Demand] ), MAXX ( Stock, Stock[Stock] ) - _demandcumulatepreviousdate )Demand: = IF ( HASONEVALUE ( Stock[ArticleCode] ) && HASONEVALUE ( Date_demand[Date Demand] ), SUM ( Demand[Demand] ) )
3 Replies
- Jihwan_Kim
Super User
Hi,
I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
WINDOW function (DAX) - DAX | Microsoft Learn
Balance: = VAR _demandcumulate = CALCULATE ( SUM ( Demand[Demand] ), WINDOW ( 1, ABS, 0, REL, ALL ( Date_demand[Date Demand] ), ORDERBY ( Date_demand[Date Demand], ASC ) ) ) RETURN IF ( HASONEVALUE ( Stock[ArticleCode] ) && HASONEVALUE ( Date_demand[Date Demand] ), MAXX ( Stock, Stock[Stock] ) - _demandcumulate )Stock: = VAR _demandcumulatepreviousdate = CALCULATE ( SUM ( Demand[Demand] ), WINDOW ( 1, ABS, -1, REL, ALL ( Date_demand[Date Demand] ), ORDERBY ( Date_demand[Date Demand], ASC ) ) ) RETURN IF ( HASONEVALUE ( Stock[ArticleCode] ) && HASONEVALUE ( Date_demand[Date Demand] ), MAXX ( Stock, Stock[Stock] ) - _demandcumulatepreviousdate )Demand: = IF ( HASONEVALUE ( Stock[ArticleCode] ) && HASONEVALUE ( Date_demand[Date Demand] ), SUM ( Demand[Demand] ) )Hii Kim
I have problem with this measure when I have hierarchy under Article Code, lets say Article Code Detail. if I breakdown by hierarchy Article Code Detail its not correct. can you help me to re-write dax Kim? thanks before
sample
A001
A001-1A001-2
A001-3
A002
A002-1A002-2
A002-3
- Jihwan_Kim
Hi,
I am not 100% sure if I understood your question correctly, but if you are adding hierarchy, I think you also need to re-define stock data.
Please provide sample data model with re-defined information, and then I can try to look into it.
Thank you.
