cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Showing trend on item consumption

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 🙂

2 ACCEPTED SOLUTIONS
Super User

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.

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

Check out my latest demo report in the data story gallery.

Stand with Ukraine!

Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

Community Support

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.

7 REPLIES 7
Community Support

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.

Helper I

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

Super User

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.

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

Check out my latest demo report in the data story gallery.

Stand with Ukraine!

Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

Helper I

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.

Super User

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.

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

Check out my latest demo report in the data story gallery.

Stand with Ukraine!

Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

Super User

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.

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

Check out my latest demo report in the data story gallery.

Stand with Ukraine!

Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

Super User

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors