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 all,
Mgmt has tasked me with calculating a daily burn rate on single use items in our inventory. Essentially we want to calculate daily consumption over time for each facility.
Here is an example of my data:
Index | Date | Facility | Item | Qty | Amount |
1 | 3/28/2020 | A | Gloves | Box | 10 |
2 | 3/28/2020 | A | Goggles | Each | 10 |
3 | 3/28/2020 | A | Masks | Each | 50 |
4 | 3/29/2020 | A | Gloves | Box | 8 |
5 | 3/29/2020 | A | Goggles | Each | 5 |
6 | 3/29/2020 | A | Masks | Each | 35 |
7 | 3/30/2020 | A | Gloves | Box | 2 |
8 | 3/30/2020 | A | Goggles | Each | 7 |
9 | 3/30/2020 | A | Masks | Each | 23 |
10 | 3/28/2020 | B | Gloves | Box | 15 |
11 | 3/28/2020 | B | Goggles | Each | 11 |
12 | 3/28/2020 | B | Masks | Each | 25 |
13 | 3/29/2020 | B | Gloves | Box | 10 |
14 | 3/29/2020 | B | Goggles | Each | 15 |
15 | 3/29/2020 | B | Masks | Each | 35 |
16 | 3/30/2020 | B | Gloves | Box | 3 |
17 | 3/30/2020 | B | Goggles | Each | 6 |
18 | 3/30/2020 | B | Masks | Each | 9 |
Our measurement period is 7-14 days. Formula is: day 1 minus day 2, day 2 minus day 3, etc. Then the average of the measurement period is taken.
My problem is that, staff are entering new inventory along with the old inventory, so an item might decrease for a period of time and then suddenly jump up in amount. The formula assumes that no new stock is being entered. Anyone have any ideas on how I could do my calculation and adjust for new inventory in dax?
I was thinking something along the lines of an IF statement and utilizing the index column??
Thanks.
Solved! Go to Solution.
Just a thought, can you ignore the negatives? So if you have day 1 - day 2 and you have 10 and 8 for those values then you have used 2 but if the next entry is 50 then you would have 8 - 50, just ignore these, exclude them from your average calculation. You could get your column with:
Daily Use =
VAR __Date = 'Table'[Date]
VAR __Item = 'Table'[Item]
VAR __Facility = 'Table'[Facility]
VAR __Qty = 'Table'[Qty]
VAR __Amount = 'Table'[Amount]
VAR __Next =
MAXX(
FILTER(
'Table',
[Date] = (__Date + 1) * 1. &&
[Item] = __Item &&
[Facility] = __Facility &&
[Qty] = __Qty
),
[Amount]
)
VAR __Diff = __Amount - __Next
RETURN
IF(__Diff < 0,BLANK(),__Diff)
@Greg_Deckler That's a great idea! I made a minor change because it was calculating the wrong way--
Before:
Date | Item | QTY | Facility | Amount | DailyUse |
3/12/2020 | Gloves | EA | A | 152 | |
3/19/2020 | Gloves | EA | A | 152 | |
3/20/2020 | Gloves | EA | A | 134 | |
3/23/2020 | Gloves | EA | A | 134 | 5 |
3/24/2020 | Gloves | EA | A | 139 | |
3/25/2020 | Gloves | EA | A | 126 | 3 |
3/26/2020 | Gloves | EA | A | 129 | |
3/27/2020 | Gloves | EA | A | 117 | |
3/30/2020 | Gloves | EA | A | 324 |
After:
Date | Item | QTY | Facility | Amount | DailyUse |
3/12/2020 | Gloves | EA | A | 152 | |
3/19/2020 | Gloves | EA | A | 152 | |
3/20/2020 | Gloves | EA | A | 134 | 18 |
3/23/2020 | Gloves | EA | A | 134 | |
3/24/2020 | Gloves | EA | A | 139 | |
3/25/2020 | Gloves | EA | A | 126 | 13 |
3/26/2020 | Gloves | EA | A | 129 | |
3/27/2020 | Gloves | EA | A | 117 | 12 |
3/30/2020 | Gloves | EA | A | 324 |
Daily Use =
VAR __Date = 'Table'[Date]
VAR __Item = 'Table'[Item]
VAR __Facility = 'Table'[Facility]
VAR __Qty = 'Table'[Qty]
VAR __Amount = 'Table'[Amount]
VAR __Next =
MAXX(
FILTER(
'Table',
------->>>[Date] = (__Date - 1) * 1. &&
[Item] = __Item &&
[Facility] = __Facility &&
[Qty] = __Qty
),
[Amount]
)
VAR __Diff = __Amount - __Next
RETURN
IF(__Diff < 0,BLANK(),__Diff)
Thanks for your help!
Just a thought, can you ignore the negatives? So if you have day 1 - day 2 and you have 10 and 8 for those values then you have used 2 but if the next entry is 50 then you would have 8 - 50, just ignore these, exclude them from your average calculation. You could get your column with:
Daily Use =
VAR __Date = 'Table'[Date]
VAR __Item = 'Table'[Item]
VAR __Facility = 'Table'[Facility]
VAR __Qty = 'Table'[Qty]
VAR __Amount = 'Table'[Amount]
VAR __Next =
MAXX(
FILTER(
'Table',
[Date] = (__Date + 1) * 1. &&
[Item] = __Item &&
[Facility] = __Facility &&
[Qty] = __Qty
),
[Amount]
)
VAR __Diff = __Amount - __Next
RETURN
IF(__Diff < 0,BLANK(),__Diff)
@Greg_Deckler That's a great idea! I made a minor change because it was calculating the wrong way--
Before:
Date | Item | QTY | Facility | Amount | DailyUse |
3/12/2020 | Gloves | EA | A | 152 | |
3/19/2020 | Gloves | EA | A | 152 | |
3/20/2020 | Gloves | EA | A | 134 | |
3/23/2020 | Gloves | EA | A | 134 | 5 |
3/24/2020 | Gloves | EA | A | 139 | |
3/25/2020 | Gloves | EA | A | 126 | 3 |
3/26/2020 | Gloves | EA | A | 129 | |
3/27/2020 | Gloves | EA | A | 117 | |
3/30/2020 | Gloves | EA | A | 324 |
After:
Date | Item | QTY | Facility | Amount | DailyUse |
3/12/2020 | Gloves | EA | A | 152 | |
3/19/2020 | Gloves | EA | A | 152 | |
3/20/2020 | Gloves | EA | A | 134 | 18 |
3/23/2020 | Gloves | EA | A | 134 | |
3/24/2020 | Gloves | EA | A | 139 | |
3/25/2020 | Gloves | EA | A | 126 | 13 |
3/26/2020 | Gloves | EA | A | 129 | |
3/27/2020 | Gloves | EA | A | 117 | 12 |
3/30/2020 | Gloves | EA | A | 324 |
Daily Use =
VAR __Date = 'Table'[Date]
VAR __Item = 'Table'[Item]
VAR __Facility = 'Table'[Facility]
VAR __Qty = 'Table'[Qty]
VAR __Amount = 'Table'[Amount]
VAR __Next =
MAXX(
FILTER(
'Table',
------->>>[Date] = (__Date - 1) * 1. &&
[Item] = __Item &&
[Facility] = __Facility &&
[Qty] = __Qty
),
[Amount]
)
VAR __Diff = __Amount - __Next
RETURN
IF(__Diff < 0,BLANK(),__Diff)
Thanks for your help!
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |