Calculate and visualize the age of distribution of the utems in stock

[Fia123]

I will appreciate very much if anyone can help me to solve this problem. I have no idea how to proceed 😞

I have a report where  calculated the age of distribution of the items in our stock. I want to know how many products are from 2018, 2017, 2016, 2015...........2010,  To do this I have a table with purchase lines (Purchase), a table with the number of items in stoch for each product (Stock) - and a date table (INVO_DATE).

The first table show the result. Eg. I have 83 items of product Vare1 in stock (se table 2). Of those are 20 purchased in 2018 and 63 in 2017 (table 1). In my calculation I assume that the oldest items are sold first. I start to compare the number of items in stock (eq. 83 for Vare1) with the number purchased latest year (eg. 20 items of Vare1 where purchased in 2018). If the number in stock is greater than the number purchased, I suppose that all the latest purchased items are in the stock. Then I continue to compare the rest of the stock (eg. 63 for Vare1) with the number of purchased 2017 (eg. 946 for Vare1)........ If the number of stock is less then the number purchased in 2018, I suppose all items in stoch were purchased in 2018.

My  challenge is to visualize the result in a column chart with purchase year along X axis and number of items in stock along Y axis. I want to se how many of the items in stock are purchased in 2018, 2017, 2016, 2015 ....... I don't know how to summing the rows and columns in my result table (se below, first table).  As you can see in the table, the  subtotals are not the sum of the rows and colums (and I understand that).

I am using the following measure to calculate how many of the items in stock are purchased in 2018, 2017, .....

NumbAge =
if('Purchase'[#In_Stock]-'Purchase'[#Purchase (PurchYear>MaxYear)]>0;
         if('Purchase'[#In_Stock]-'Purchase'[#Purchase (PurchYear>>MaxYear)]>0;
              'Purchase'[#Purchase];
              'Purchase'[#In_Stock]-'Purchase'[#Purchase (PurchYear>MaxYear)]);
        0)

where
#In_Stock = DIVIDE(sum('Purchase'[In_Stock]);COUNTROWS('Purchase'))

#Purchase (PurchYear>MaxYear) = CALCULATE('Purchase'[#Purchase];Filter(ALL(INVO_DATES);INVO_DATES[Year]>(Max(INVO_DATES[Year]))))

#Purchase (PurchYear>>MaxYear) = CALCULATE('Purchase'[#Purchase]; Filter(ALL(INVO_DATES); INVO_DATES[Year]>= Max(INVO_DATES[Year])))

#Purchase = sum('Purchase'[Purchased])

I find it hard to explain this in English so I hope somebody takes the time to try to understand.

Thanh you!

Tove

[Greg_Deckler]

Can you provide raw example data? Tough to recreate and play around with this without it.

[Fia123]

Thank you very much 🙂 

I try to attach a pbix-file with example data and the data model, but I didn't find out how to do it. Maybe is not possible 😞  But here comes some example data:

Purchase lines

In stock

Date table:

All measures:

This table gives correct number of items for each product - but the subtotals are not correct

This is a type of a graph I am trying to get - with the purchase year along the x axis and the number of items in stock along the y axis. In other words how many items in the stock are purchased in 2012, 2013, 12014 ...  Today it is not correct. It shows the same as the subtotals in the table.

I'm very excited and curious about what you're up to 🙂

Tove