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.
Hi,
I have some store data and need DAX to calculate remaining stock in iventory along with purchase cost
Example:
In this table I have two Branches and each has two procucts, On Jan the total purchase of I10 product in Branch1 is 10 and cost per unit is $21. when there us no sale of I20 on Jan in the Branch1 then this count should come under "Last month remaining quantity" on Feb
Note: Out of the 10 "I10" products if 2 sold on Jan then the remaining count "8" should come under "Last month remaining quantity" on Feb along with purchased cost
DAX should get result into "Last month remaining quantity" after filtering Branch and Products
Product | Branch | Month | Type | Quantity | Amount | Last Month remaining quantity | Price per Unit |
I10 | Branch1 | Jan | Purchase | 10 | 21 | ||
I10 | Branch1 | Feb | Purchase | 10 | 49 | 10 | 21 |
I10 | Branch1 | Mar | Purchase | 10 | 29 | 10 | 49 |
I10 | Branch1 | Apr | Purchase | 10 | 46 | 10 | 29 |
I20 | Branch1 | May | Purchase | 10 | 47 | ||
I20 | Branch1 | June | Purchase | 10 | 22 | 10 | 47 |
I20 | Branch1 | July | Purchase | 10 | 17 | 10 | 22 |
I20 | Branch1 | Aug | Purchase | 10 | 14 | 10 | 17 |
I10 | Branch2 | Jan | Purchase | 10 | 30 | ||
I10 | Branch2 | Feb | Purchase | 10 | 40 | 10 | 30 |
I10 | Branch2 | Mar | Purchase | 10 | 23 | 10 | 40 |
I10 | Branch2 | Apr | Purchase | 10 | 40 | 10 | 23 |
I20 | Branch2 | May | Purchase | 10 | 29 | ||
I20 | Branch2 | Jun | Purchase | 10 | 31 | 10 | 29 |
I20 | Branch2 | Jul | Purchase | 10 | 28 | 10 | 31 |
I20 | Branch2 | Aug | Purchase | 10 | 31 | 10 | 28 |
Thank you!
Solved! Go to Solution.
Hi Reddy
Not sure how the real data looks like and some info is missing such as the the quantity sold. However, based on the available information and data:
1st you need to have a month number column. Create a new column
Month Number =
VAR CurrentMonth = Stores[Month]
RETURN
SWITCH (
TRUE (),
CurrentMonth = "Jan", 1,
CurrentMonth = "Feb", 2,
CurrentMonth = "Mar", 3,
CurrentMonth = "Apr", 4,
CurrentMonth = "May", 5,
CurrentMonth = "June", 6,
CurrentMonth = "July", 7,
CurrentMonth = "Aug", 8,
CurrentMonth = "sep", 9,
CurrentMonth = "Oct", 10,
CurrentMonth = "Nov", 11,
CurrentMonth = "Dec", 12
)
For last month quantity create a new column
Last Month remaining quantity =
VAR StoreBranchTable =
CALCULATETABLE ( Stores, ALLEXCEPT ( Stores, Stores[Product], Sores[Branch] ) )
VAR CurrentMonthNum = Stores[Month Number]
RETURN
MINX (
FILTER ( StoreBranchTable, Stores[Month Number] = CurrentMonthNum - 1 ),
Stores[Quantity]
)
For last month amont create a new column
Price per Unit =
VAR StoreBranchTable =
CALCULATETABLE ( Stores, ALLEXCEPT ( Stores, Stores[Product], Sores[Branch] ) )
VAR CurrentMonthNum = Stores[Month Number]
RETURN
MINX (
FILTER ( StoreBranchTable, Stores[Month Number] = CurrentMonthNum - 1 ),
Stores[Amount]
)
Hi Reddy
Not sure how the real data looks like and some info is missing such as the the quantity sold. However, based on the available information and data:
1st you need to have a month number column. Create a new column
Month Number =
VAR CurrentMonth = Stores[Month]
RETURN
SWITCH (
TRUE (),
CurrentMonth = "Jan", 1,
CurrentMonth = "Feb", 2,
CurrentMonth = "Mar", 3,
CurrentMonth = "Apr", 4,
CurrentMonth = "May", 5,
CurrentMonth = "June", 6,
CurrentMonth = "July", 7,
CurrentMonth = "Aug", 8,
CurrentMonth = "sep", 9,
CurrentMonth = "Oct", 10,
CurrentMonth = "Nov", 11,
CurrentMonth = "Dec", 12
)
For last month quantity create a new column
Last Month remaining quantity =
VAR StoreBranchTable =
CALCULATETABLE ( Stores, ALLEXCEPT ( Stores, Stores[Product], Sores[Branch] ) )
VAR CurrentMonthNum = Stores[Month Number]
RETURN
MINX (
FILTER ( StoreBranchTable, Stores[Month Number] = CurrentMonthNum - 1 ),
Stores[Quantity]
)
For last month amont create a new column
Price per Unit =
VAR StoreBranchTable =
CALCULATETABLE ( Stores, ALLEXCEPT ( Stores, Stores[Product], Sores[Branch] ) )
VAR CurrentMonthNum = Stores[Month Number]
RETURN
MINX (
FILTER ( StoreBranchTable, Stores[Month Number] = CurrentMonthNum - 1 ),
Stores[Amount]
)
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 |
---|---|
48 | |
24 | |
20 | |
14 | |
12 |
User | Count |
---|---|
57 | |
49 | |
43 | |
19 | |
18 |