Forum Discussion
Use DAX and M-Query to get the previous row values into new column.
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.
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))
1 Reply
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))