Forum Discussion
Converting a basic Excel formula into DAX for a new column in Power BI
I'm trying to create a new column in Power BI Desktop which replicates a basic Excel formula which seems simple enough. The Excel formula which I am trying to replicate is in column D: =IF(B2=...
You could shift one column using:
Shifted column = LOOKUPVALUE(Table1[Cage No];Table1[Index Order];Table1[Index Order]-1)
or you could get the result directly by using:
Reported size = IF(Table1[Cage No]=LOOKUPVALUE(Table1[Cage No];Table1[Index Order];Table1[Index Order]-1);0;Table1[Size])
Johanno
Continued Contributor
Continued ContributorGood question. After searching I think you would have to first create a new column that is shifted one level from the first. Then you can check which rows are equal. Those rows should return blanks on the rest the size. This seems similar:
https://community.powerbi.com/t5/Desktop/How-to-subtract-current-row-from-prior-row-and-so-on-in-power-bi/td-p/179806
Let us know if it works.
https://community.powerbi.com/t5/Desktop/How-to-subtract-current-row-from-prior-row-and-so-on-in-power-bi/td-p/179806
Let us know if it works.