Compare values in same column - Direct query Power BI

[KumarGS]

Hi,

I have a requirement where I need to compare values from same column.

For example, assume below table

Here, I need to add a new column and compare the 'Value' column data with it's previous row value. If the current value is greater than the previous, then it should store 1 and if the current value is lesser than the previous, then it should store 0. (as below)

Please let me know how this can ben achieved.

Thanks!

[v-stephen-msft]

Hi @KumarGS ,

You need to add the index column to Power Query first(Adding index columns is allowed).

If you also want to create group index with Power Query, please kindly refer to

How to create group index with Power Query or R

Then go back to Power BI Desktop, create a measure to get the previous value.

Pervious value = CALCULATE(SUM('Table'[Value]),FILTER(ALLSELECTED('Table'),[Index]=MAX('Table'[Index])-1))

Now you can get your expected result by creating another measure.

New value = IF(ISBLANK([Pervious value]),BLANK(),IF([Pervious value]>SUM('Table'[Value]),0,1))

Also, you can get previous valu in Power Query, please kindly refer to

Value from previous row – Power Query, M language

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[KumarGS]

Hi Stephen,

Thanks for your response. I tried to add an Index column at first and it was not getting supported in direct query mode. I am getting the below error and couldn't proceed further. Please advise.

Thanks!