Burn Rate Over Time

[StephenK]

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.

[Greg_Deckler · Accepted 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 · Accepted 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)

[StephenK · Accepted solution]

@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!

[Anonymous]

Indeed, it would be a great idea if it was correct. But it's not. This calculation does not return the true numbers of usage but just "some" numbers that sometimes are correct and sometimes are not. What the average is... is not interpretable at all.

This is easy to prove. Let's say that on one day the number is 100. The next day it's 200. The truth is that the usage from 100 to 200 could be any number from 0 to 100. ANY NUMBER. What your calculation does is it ignores this completely, treating the day as non-existent in the calculation of the average.

That is certainly not a true-to-life calculation and can be way off.

Best
D

[Anonymous]

"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."

No, you can't do what you want because there is no way with the above data to know how many new items were added if they were added. If in a day some items were given away and some were added to the inventory, there is no way to know individual amounts from the total.

You have to explicitly capture both amounts.

Best
D