topic Index or Rank for month with Stock-Value in DAX Commands and Tips

Index or Rank for month with Stock-Value

froebm — Wed, 29 Nov 2023 17:32:00 GMT

Hi Community,

I have a table with the following structure:

The articles do not have a stock quantity in each month. I would now like to write a DAX formula that assigns a rank or index to the month.
For example, if item X has stock quantities in the months of June, August and December, the rank/index should correspond to 1 for June, 2 for August and 3 for December.

Thanks in advance,

Max

Re: Index or Rank for month with Stock-Value

Jihwan_Kim — Wed, 29 Nov 2023 18:31:29 GMT

Hi,

I am not sure how your desired outcome looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

It is for creating a new column, and the result is partitioned by the year and the article number.

I hope the below can provide some ideas on how to create a solution for your semantic model.

ROWNUMBER function (DAX) - DAX | Microsoft Learn

expected result CC = ROWNUMBER ( Data, ORDERBY ( Data[EndOfMonth], ASC ), DEFAULT, PARTITIONBY ( Data[Year], Data[Article No] ), MATCHBY ( Data[EndOfMonth], Data[Article No], Data[Year] ) )

Re: Index or Rank for month with Stock-Value

froebm — Thu, 30 Nov 2023 07:36:32 GMT

Great, worked perfectly. Thank you very much!