cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

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?

2 REPLIES 2
Frequent Visitor

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?
Super User

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.