Divide [target] by [result] in a different row [period]

[Anonymous]

Hi all,

Im working in Power Query in Excel and I need to find the percent difference between a target and a result in a previous period. I have several half yearly periods in my database, eg 2018B, 2019A, 2019B, 2020A etc. 

At present, my database has column headers and data representing:

unit | period | result | target | 

widgetA | 2019A | 4 | 5 | 

widgetB | 2019B | null | 6 | 

So, I need to calculate: target for 2019B / result for 2019A, and then format as a percent. (And calculate this for all the periods). Answer for above would be 6/4 = 150%

Thanks for your help, Rogerbij

[Stachu · Accepted solution]

you are not gonna use it in PowerPivot right? if yes then I think it's better to calculate it there with DAX, as you won't have the classic ratio&aggregation issues

as for the M solution - I suggest putting previous result in the same row using @ImkeF  technique described here
https://www.thebiccountant.com/2018/07/12/fast-and-easy-way-to-reference-previous-or-next-rows-in-po...

then you just divide with simple calculated column and remove the one representing previous row

[Anonymous]

Hi @Stachu , 

Thanks for this, and an interesting read. I think since I only have limited use for this Im going to cheat and just calculate a few summary tables and by-pass this altogether. 

Best, Roger