Prior row value and Percentage change for non-sequential indexed rows

[pjukone]

Dear all,  I am new to using dax and was wondering if you could help.  In the report below I was trying to calculate the % change where the report is from multiple table sources.  The data is in a way so there are multiple dates from each underlying table, hence I just used the month number to remove the issue of showing multiple lines as the sale value is a calculation based on the totals for the month.  In this, I was trying to calculate the % change based on the prior row.  I have seen many examples of this where a clear date/month / year, is straightforward, although I was trying to do this based on the Line ref (as shown below).  

The calculations are as follows:

LineRef = min ('Date'[Ref] )
Prior row value = CALCULATE ( [Net Sales], Dateadd( 'Date'[Date] , -1 , MONTH ))

% Change = DIVIDE( ( [Net Sales] - [Prior row value]) , [Prior row value] )

RankRef = RANK.EQ ([LineRef] , 'Date'[Ref] , ASC )

Although I was struggling to get the Prior row value and % change to work due to the LineRef not being sequential, therefore I could not use current row less one etc to calculate the prior row.  The issue is the multiple dates to use Dateadd .  I also tried to use rankx to get a sequential number but that did not work.  The idea being if Rankx worked I could pick up the prior row based on current rank less 1.  

I was wondering if there was a way to get the above to work, and needed your help with a solution that may work in this situation?

[pjukone]

Hi TamerJ1 , thank you for your help with this,  I got the reference number in order, and that worked  (thanks, I would have not thought of putting the rankx in the date table by Month & year).  But now I have a different (but related issue) in calculating the prior row.   The result I get is the same value as the net sales in the same row.  I tried removefilter()  and all() and I get the same result. 

The 'Date'[sRef] is the new fields with the rank (as per the above suggestion.  so 'Date'[sRef] -1 gives the correct index.  The only challange is getting the correct [Net Sales) which is a calculated measure (not on a table)
 

Prior row value =

calculate ( [Net sales] , 'Date'[sRef] -1 , all ('Date'[sRef]) )

Please can you help with this?

[tamerj1]

Hi @pjukone 

easiest way would be by creating a new calculated column in the Date table: Year-Month sequential number. For example you can try

RANKX ( 

'Date',

'Date'[Year] * 100 + 'Date'[Month],,

ASC,

DENSE

)

then you can minus 1 to get to the orevious month comfortably.