Cumulative Count of last avaialble stock value

[patlopes]

Hi,

Having some trouble finding the right approach with a DAX measure, maybe someone can help.

I have the following scenario, Category and Product, and the available stock at given date to which corresponds one Version (Version is unique in each Product).

Category	Product	Version	Published Date	# Items
Shoes	Boots	7	23/09/2021	33
Shoes	Boots	8	28/10/2021	60
Shoes	Boots	9	28/10/2021	47
Shoes	Boots	10	21/01/2022	50
Shoes	Trainers	7	23/09/2021	20
Shoes	Trainers	8	28/10/2021	32
Shoes	Trainers	9	28/10/2021	58
Shoes	Trainers	10	24/01/2022	33
Bottoms	Trousers	2	28/10/2021	34
Bottoms	Trousers	3	04/01/2022	10
Bottoms	Trousers	4	19/01/2022	15
Bottoms	Trousers	5	19/01/2022	13
Bottoms	Trousers	6	26/01/2022	35
Bottoms	Trousers	7	02/02/2022	40
Bottoms	Shorts	2	28/10/2021	27
Bottoms	Shorts	3	17/12/2021	5
Bottoms	Shorts	4	19/01/2022	12
Bottoms	Shorts	5	27/01/2022	63
Bottoms	Shorts	6	27/01/2022	36
Bottoms	Shorts	7	27/01/2022	27
Bottoms	Leggings	2	28/10/2021	23
Bottoms	Leggings	3	04/01/2022	25
Bottoms	Skirts	2	28/10/2021	30

For some products there are no values in some months - in this case I want to count the last available version.

Sometimes one product has more than one count per month - in this case I want to count only the last version in the month.

The final outcome should look as below.

The model has the table with the counts connected to a calendar table(continuous).

The x-axis in the graph is from Calendar table.

Any help is appreciated,

Thank you

[CNENFRNL · Accepted solution]

Simple enough

Stock = 
VAR __yyyymm = MAX( DATES[YYYYMM] )
RETURN
    CALCULATE(
        SUMX(
            VALUES( INVT[Category] ),
            CALCULATE(
                SUMX(
                    VALUES( INVT[Product] ),
                    CALCULATE(
                        MAXX(
                            TOPN( 1, INVT, INVT[Published Date], DESC, INVT[Version], DESC ),
                            INVT[# Items]
                        )
                    )
                )
            )
        ),
        DATES[YYYYMM] <= __yyyymm
    ) + 0

[ValtteriN]

Hi,

You can use this kind of pattern to find the latest amount: 

Var cdate = max(table[Date])
var latest_ver = calculate(max(table[version]),all(table[Date]),table[Date]<=cdate)
return

CALCULATE(SUM(Table[items]),ALL(Table[Date]),Table[version]=latest_ver)
Ping me with @ if your have questions/problems

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[patlopes]

Hi @ValtteriN 

thank you for your quick reply. It's almost there, just one parameter missing. 

With your formula (thank you for it) i get below result.  

In last month, 022022,  I only see one product, although I have stock for other products.

In 022022 I would like to see sum of all last versions per product independent of when they where published. 

 Thank you

[ValtteriN]

Hi,

You can use the above logic + sumx to get the values for all the products.