Showing trend on item consumption

[vpsoini]

Hi.

I have table containing inventory changes of group of items (ledger) over time.  It contains data of all changes in inventory over time in item level.

I would need to get a trend of item consumption per week over the last 18 months in week level.  How should I approach that?

I have calendar table to match dates into weeks and I have item's inventory changes per dates.  Example below.  A single item can have both positive and negative inventory changes and here I'm only interested in negative changes (consumption). So if I have:

Item | Date | Change | Inventory

001 | 1/4/2021 | -2 | 48 

001 | 5/4/2021 | -3 | 45 

001 | 12/4/2021 | -1 | 44 

001 | 13/4/2021 | 6 | 50 

001 | 14/4/2021 | -4 | 46 

001 | 20/4/2021 | -2 | 44

So the consumption has been:

Week 13: 2

Week 14: 3

Week 15: 5

Week 16: 2

If I get those values per week out, then it's easy to put that measure(?) into visuals over time.

Help tackling this is highly appreciated 🙂

[ERD · Accepted solution]

@vpsoini ,

One of the ways is to use the next measures (examples are for 7 days. I'm assuming you have a separate Date table connected to your data table via Date column):

last7days = 
CALCULATE (
    [ChangeAmt],
    DATESINPERIOD ( 'Date'[Date], MAX ( 'T'[Date] ), -7, DAY )
)

prev7days = 
CALCULATE(
    [ChangeAmt],
    DATESINPERIOD ( 'Date'[Date], MAX ( 'T'[Date] ) - 7, -7, DAY )
) 

compare7days = 
VAR a = [last7days]
VAR b = [prev7days]
RETURN
    IF ( HASONEVALUE ( T[Item] ), ( a - b ) / b )

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

[v-angzheng-msft · Accepted solution]

Hi, @vpsoini 

@ERD  has provided a solution, and I came up with another solution that I hope will help as well.

I offer another solution for your reference, which does not require you to have a separate date table.

last 7 days:

last 7 days = 
var _maxDate=CALCULATE(MAX('Table'[Date]),ALL('Table'))
var _sum=
    CALCULATE(
        SUM('Table'[Change]),
        FILTER(
            ALL('Table'),
            'Table'[Change]<=0&&
            'Table'[Item]=MAX('Table'[Item])&&
            'Table'[Date]<=_maxDate&&
            'Table'[Date]>_maxDate-7
            )
        )
var _abs=ABS(_sum)

return _abs

to change the _maxDate-N to get other measures:
last 7(30) days, previous 7(30)days:

cons. change:

CMP_30days = DIVIDE([last 30 days]-[previous 30days],[previous 30days])

Result:

Please refer to the attachment below for details. Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vpsoini]

Hi @amitchandak & @ERD 

Thanks for the suggestions.  Unfortunately my source changed a bit (inventory shows now only zero, so cannot be used) and also my goal changed a bit, so if you could help tackling to get values into a visual table so, that i would have

Item | consumption last 7 days | consumption change last 7 days -> last 14-7 days in % | consumption last 30 days |  consumption change last 30 days -> last 60-30 days in %|

So I'm looking for

• How many items are consumed during last 7 days 
• How much consumption has changed last 7 days compared to previous 7 days in % (trend)
• How many items are consumed during last 30 days 
• How much consumption has changed last 30 days compared to previous 30 days in % (trend)

Thanks for your valuable replies so far and sorry for the inconvinience for the extra hassle...

[ERD]

@vpsoini ,

Since you have some changes, please, provide for your new requirements and new data

1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

[vpsoini]

Hi @ERD 

Data looks like this (sample with 2 different itms, real one has about 50k items). im interested in negative changes only (consumption), so any positive changes (adding inventory) is not in the scope here.

Item	Date	Change
001	1.10.2021	-1
001	2.10.2021	-3
002	2.10.2021	-2
001	5.10.2021	-2
001	8.10.2021	6
002	9.10.2021	-2
001	11.10.2021	-1
002	13.10.2021	-2
001	16.10.2021	-3
002	17.10.2021	5
001	20.10.2021	-2
001	21.10.2021	-1
002	22.10.2021	-1
001	25.10.2021	-2
002	25.10.2021	-1
001	26.10.2021	-1

So the data I'm looking for is (let's assume, that in september (previous 30days) the consumption for item 001  was 12 and for item 002 it was 9)

Item	consumption last 7 days	consumption previous 7 days	cons. change last 7 days compared to previous 7 days	consumption last 30 days	consumption previous 30 days	cons. change last 30 days compared to previous 30 days
001	6	3	( 3->6 = ) +100%	16	12	(12 -> 16 = ) 33%
002	2	2	(2 -> 2= ) 0%	8	9	(9 ->8 = ) - 11%

Once again, any help tackling those measures (or columns?) is very appreciated.

[ERD · Accepted solution]

@vpsoini ,

One of the ways is to use the next measures (examples are for 7 days. I'm assuming you have a separate Date table connected to your data table via Date column):

last7days = 
CALCULATE (
    [ChangeAmt],
    DATESINPERIOD ( 'Date'[Date], MAX ( 'T'[Date] ), -7, DAY )
)

prev7days = 
CALCULATE(
    [ChangeAmt],
    DATESINPERIOD ( 'Date'[Date], MAX ( 'T'[Date] ) - 7, -7, DAY )
) 

compare7days = 
VAR a = [last7days]
VAR b = [prev7days]
RETURN
    IF ( HASONEVALUE ( T[Item] ), ( a - b ) / b )

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

[ERD]

Hi @vpsoini ,

one of the options is to use this measure:

consumption =
SUMX ( FILTER ( 'Table', 'Table'[Change] < 0 ), 'Table'[Change] * -1 )

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

[amitchandak]

@vpsoini , if the change is a measure

calculate([Inventory], filter(Values(Table[Week]), [Change] <0 ))

if changes is column

calculate([Inventory], filter(Table, [Change] <0 ))

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8