Skip to main content
cancel
Showing results for 
Search instead 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

Reply
vpsoini
Helper I
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

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

ERD_0-1635436942691.png

 

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!

View solution in original post

v-angzheng-msft
Community Support
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:

vangzhengmsft_0-1635487175398.png

 

cons. change:

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

 

Result:

vangzhengmsft_1-1635487485807.png

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.

 

 

 

View solution in original post

7 REPLIES 7
v-angzheng-msft
Community Support
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:

vangzhengmsft_0-1635487175398.png

 

cons. change:

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

 

Result:

vangzhengmsft_1-1635487485807.png

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
Helper I
Helper I

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

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

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!

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.

 

ItemDateChange
0011.10.2021-1
0012.10.2021-3
0022.10.2021-2
0015.10.2021-2
0018.10.20216
0029.10.2021-2
00111.10.2021-1
00213.10.2021-2
00116.10.2021-3
00217.10.20215
00120.10.2021-2
00121.10.2021-1
00222.10.2021-1
00125.10.2021-2
00225.10.2021-1
00126.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)

 

Itemconsumption last 7 daysconsumption previous 7 dayscons. change last 7 days compared to previous 7 daysconsumption last 30 daysconsumption previous 30 dayscons. change last 30 days compared to previous 30 days
00163( 3->6 = ) +100%1612(12 -> 16 = ) 33%
00222(2 -> 2= ) 0%89(9 ->8 = ) - 11%
       

 

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

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

ERD_0-1635436942691.png

 

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!

ERD
Super User
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!

amitchandak
Super User
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 ))

 

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

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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