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 am currently having trouble on creating a measure to be able to show the beginning of the month inventory. The objective is that the table would show the existing inventory based on the date selected (ex. Jan 28) as well as the beginning of the month inventory based on the earliest date (with values), which is based on the month selected.
Need help on how to create a working measure 'Beg of the Month Inventory". It should show inventory for 1/7/2020 which is 38K and ignore the Date Slicer. Image below
Thanks in advance!
Solved! Go to Solution.
Thank you for everyone's help! I took some pieces from the advise and came up with this
Beg of Month Inventory =
VAR starting = CALCULATE(FIRSTDATE('Table'[Date]), ALL('DATE TABLE'[Date]))
RETURN
CALCULATE(SUM('Table'[Inventory Qty]),FILTER(ALL('DATE TABLE'[Date]),'DATE TABLE'[Date]=starting))
Works now. Thank you!
Hi @e9801591 ,
First off, how do you compute for the inventory amount? And please post a sample data.
If Inventory was a balance sheet account, I would compute for its cumulative total from the earliest transaction date so in DAX it would be something like
nventory Balance =
CALCULATE (
SUM ( JournalsTable[Amount] ),
FILTER (
ALL ( DatesTable[Date] ),
DatesTable[Date] <= MAX ( DatesTable[Date] )
)
)
then the beginning inventory would be the cumulative total prior to the current period/date
Beginning Inventory Balance =
CALCULATE (
SUM ( JournalsTable[Amount] ),
FILTER (
ALL ( DatesTable[Date] ),
DatesTable[Date] < MIN ( DatesTable[Date] )
)
)
But this may not be the case for you.
Proud to be a Super User!
Thank you for the quick reply @danextian . My source table contains Inventory count per day so my Beginning of the month Inventory would just be a sum of Inventory Qty for that day (earliest date with value) for the month.
Example data
Date | Item | Inventory Qty |
1/7/2020 | Item A | 100 |
1/7/2020 | Item B | 20 |
1/28/2020 | Item A | 50 |
1/28/2020 | Item B | 10 |
I hope the above solution would have solved it. If not try
Start of month =calculate(sum(Table[Qty]),Filter('Date',Date[Date] = startofmonth(Date[Date] )))
End of month =calculate(sum(Table[Qty]),Filter('Date',Date[Date] = endofmonth(Date[Date] )))
Thank you for everyone's help! I took some pieces from the advise and came up with this
Beg of Month Inventory =
VAR starting = CALCULATE(FIRSTDATE('Table'[Date]), ALL('DATE TABLE'[Date]))
RETURN
CALCULATE(SUM('Table'[Inventory Qty]),FILTER(ALL('DATE TABLE'[Date]),'DATE TABLE'[Date]=starting))
Works now. Thank you!
Hi @e9801591 ,
Some other users might have another takes on this but here's mine:
First, create a calculated column to index the dates per inventory item.
Date Index Per Item =
RANKX (
VALUES ( Inventory[Date] ),
CALCULATE (
MIN ( 'Inventory'[Date] ),
ALLEXCEPT ( 'Inventory', 'Inventory'[Date] ),
Inventory[Item] = EARLIER ( Inventory[Item] )
),
,
ASC,
DENSE
)
Second, you may either create a calculated column for the inventory value the immediately preceding day in the table or create a measure
Inventory Qty Prev Day Column =
CALCULATE (
SUM ( Inventory[Inventory Qty] ),
FILTER (
ALL ( Inventory ),
Inventory[Item] = EARLIER ( Inventory[Item] )
&& Inventory[Date Index Per Item]
= EARLIER ( Inventory[Date Index Per Item] ) - 1
)
)
Inventory Qty Prev Day Measure =
SUMX (
SUMMARIZE (
Inventory,
Inventory[Item],
Inventory[Date Index Per Item],
"Value", CALCULATE (
SUM ( Inventory[Inventory Qty] ),
FILTER (
ALL ( Inventory ),
Inventory[Item] = EARLIER ( Inventory[Item] )
&& Inventory[Date Index Per Item]
= EARLIER ( Inventory[Date Index Per Item] ) - 1
)
)
),
[Value]
)
Proud to be a Super User!
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 |
---|---|
110 | |
97 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |