Use DAX and M-Query to get the previous row values into new column.

[Anonymous]

Hai All,

Question: Need solution in both M-QUERY and DAX to get the previous row value into a new column.

The below table represents a sample data representing the price for the corresponding Dates.

Pcd-Price for current date

ppdr-Price for previous date reference.

ppd-price for previous date

Here we considered the data which is from date 10 to 19(refer above table).

 For Example

If we consider date '19' in 1st row.... pcd column 1st row should have 19 date pcd value and new ppd column should have 18 date pcd value in !st row  (Check the arrow marks in the above data)

Case: If each and every row goes one row up, then we may find the last row in newly created column(ppd) as BLANK/Null then in that place we need to replace it with the previous date(9) pcd value.

Note:

--> The newly created column(PPD) should be equal to PPDR column ( Do not use PPDR ,It is only for reference purpose)

-->Please provide solution in M-QUERY and DAX

Thanks in advance. Expecting earlier solution for this.

[Vera_33 · Accepted solution]

Hi @Anonymous 

If you column [S.No] is like Index, use this column, otherwise add an index column, M here

Table.AddColumn(#"Changed Type", "ppd_M", each #"Changed Type"[pcd]{[S.No]}?)

DAX here

ppd_DAX = 
VAR CurNo = 'Table'[S.No]
RETURN
CALCULATE(VALUES('Table'[pcd]),FILTER(ALL('Table'),'Table'[S.No]=CurNo+1))