@Fia123 wrote:
Hi , I need help with the following issue:
I want to show the age distribution of inventory in a tabel/chart. The calculation of the age distribution for each year is like this:
2017 = (Inventory 2017) + (Purchase 2017) - (Sold 2017)
2016 = (Inventory 2017) + (Purchase 2017 and 2016) - (Sold 2017 and 2016)
2015 = (Inventory 2017) + (Purchase 2017 and 2016 and 2015)) - (Sold 2017 and 2016 and 2015)
...
My problem is how to calculate the accumulation summaries over several years.
I highly appreciate suggestions 🙂
@Fia123
It looks like you need 4 measures.
Inventory =
CALCULATE (
SUM ( YourTable[Inventory] ),
FILTER (
ALL ( YourTable ),
YEAR ( YourTable[date] ) = YEAR ( MAXX ( ALL ( YourTable ), YourTable[date] ) )
)
)
Purchase =
CALCULATE (
SUM ( YourTable[Purchase ] ),
FILTER (
ALL ( YourTable ),
YEAR ( YourTable[date] ) >= YEAR ( MAX ( YourTable[date] ) )
)
)
Sold =
CALCULATE (
SUM ( YourTable[Sold] ),
FILTER (
ALL ( YourTable ),
YEAR ( YourTable[date] ) >= YEAR ( MAX ( YourTable[date] ) )
)
)
resultExpected =[Inventory]-[Purchase]-[Sold]
Then, say in a table visual, choose the date column and put the measure resultExpected. For more specific suggestion, please post some sample data(in plaint text or in a uploaded file, we can't copy data from a snapshot).
