How to display end of period inventory in line chart ? (no sum)

[Cyriltra]

Hello,

each row in my database record is like this:

Processing Date (dd/mm/yy)	Region	Sales	EOP Inventory
21/02/20	Americas	1000	300
22/02/20	Europe	1500	400
23/02/20	Asia	500	250

And i have a master calendar that derives weeks, month, quarter, years etc. from [Processing Date] field from past 3 years to year + 1 (so 2021 here)

I then display my sales in line chart (weekly, monthly, yearly) and using below definition which is working fine:

Sum({$<[454.year]={$(=Only([454.year]))}>}[Sales]) for current year

Sum({$<[454.year]={$(=Only([454.year])-1)}>}[Sales]) for year -1

Now want to also do a line chart but for my inventory. But I cannot use sum as it does not make sense here. So depending on the period set as dimension (ie can be by week, or quarter or year etc.),  I want to display the value at the end of this period.

Let's say my dimension is to display by week (so week 1 to week 52), I want the chart to display the inventory at the end of each week).

Browsing around, it seems a possible solution is to use firstSortedValue(), but I 'm not sure how to use it. I tried below: 

firstSortedValue([End Inv_C US$], -[Processing Date])

But it does not work....

Also, same as i did for my sales, I also need to display my end of period inventory for the year-1

Thank you

[DavidM]

Do you have days in you calendar? Use the last day of week (Fri, Sun, whatever you consider as last) in the set analysis, something like this:

Sum({$<[Day]={'Sun'}>}[EOP Inventory]) - if you have weeks as dimension in your chart, it will show you value only for Sunday.

[Cyriltra]

Hi, 

thank you it's working fine when i display my chart with weekly dimensions, but how can i do with Month and Quarter ?

[DavidM]

I suppose you want always the last day, right? Try this:

Sum({$<[Date]={"$(=Max(Date))"}>}[EOP Inventory])

[sunny_talwar]

This looks to be okay... what dimensions do you have in your chart where you are using this?

FirstSortedValue([End Inv_C US$], -[Processing Date])

[Brett_Bleess]

You received a couple more posts and no further responses, so I am assuming you got a solution, please be sure you close out your thread by using the Accept as Solution button on the post(s) that helped you with things, or if you figured it out on your own, post what you did and then mark that post.  Marking things closes out the thread and gives the posters credit for the help and lets other Members know what worked.  If you still have questions, please leave an update post.

Regards,
Brett