rank with inventory value of previous month

[tonny_bwg]

Dear Guru：

I want to write measure to rank material with the inventory value of previous month.  I've created a measure to rank the material with current inventory value(selected period). I want to show the rank change between selected period and (selected period -1). below is the measure I create:

Rank_test =

  VAR items = ALLSELECTED(fct_Inventory)

  VAR _curvalue = [Sum of Value]

  VAR _VRank = COUNTROWS( FILTER( items, fct_Inventory[TOTAL_VALUE] >=_curvalue))

  Return

   if(_VRank=0,1,_VRank)
How to modify it. BTW,  I've applied these slicers on the page to select plant, material type, period.

thanks for the help.

--Tonny

[v-yangliu-msft]

Hi  @tonny_bwg ,

You can try using the following dax:

Measure =
var _selectYearMonth=SELECTEDVALUE('Table'[YearMonth])
var _date=
MINX(
    FILTER(ALL('Table'),
    'Table'[YearMonth]=_selectYearMonth),[Date])
var _maxdate=
EOMONTH(_date,-1)
var _mindate=
EOMONTH(_date,-2)
return
RANKX(
    FILTER(ALLSELECTED('Table'),
    'Table'[Date]>_mindate&&'Table'[Date]<=_maxdate),CALCULATE(SUM([Value])),,ASC)

Can you share sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[tonny_bwg]

could you please help to solve this issue? 

[tonny_bwg]

Hi，Mr. LiuYang

appreciated your help. please use this sample file to create previous month's rank. thanks 

sample pbix

[tonny_bwg]

the fact table is as below, and a Dim_date table, a dim_Area tbl, Dim_material table link to fact table.  
my question is how to calcualte previous month rank based on current filter context.

thanks for your help.
--Tonny