cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Dividing rows in table

Hi,

I am new to power bi and am trying to transfer my r code into steps in the power bi query

• The r code itterates through each row of the data frame and divides the value in row (a) by the value in row (a-1): value_a / value_(a-1)

I have a large table with a date and value as follows:

I want to create a new column which takes the value of each row and divides it by the value by that of the pervious row -a seemingly easy task. i.e. the calculated column will be 0 for the first row and then 300/400 for the second row and 250/300 for the third row and so on.

I have spent hours looking for the solution for this and have come up empty as often the solutions uses functions that are not available in the query when creating custom column

Thank you so much for your help!

1 ACCEPTED SOLUTION
Memorable Member

this may help get you on your path - it doesn't do the calculation but does bring in the value from the prior day/row:

terms are generic - first is a calculated column approach:

PreDaySessions_Column =

VAR CurrentDay = Table1[Date]

VAR PreDay =

CALCULATE ( MAX ( Table1[Date] ), FILTER ( Table1, Table1[Date] < CurrentDay ) )

RETURN

LOOKUPVALUE ( Table1[SessionS], Table1[Date], PreDay )

this is a Measure

PreDaySessions_Measure =

VAR PreDay =

PREVIOUSDAY ( Table1[Date] )

RETURN

LOOKUPVALUE ( Table1[SessionS], Table1[Date], PreDay )

4 REPLIES 4
Memorable Member

this may help get you on your path - it doesn't do the calculation but does bring in the value from the prior day/row:

terms are generic - first is a calculated column approach:

PreDaySessions_Column =

VAR CurrentDay = Table1[Date]

VAR PreDay =

CALCULATE ( MAX ( Table1[Date] ), FILTER ( Table1, Table1[Date] < CurrentDay ) )

RETURN

LOOKUPVALUE ( Table1[SessionS], Table1[Date], PreDay )

this is a Measure

PreDaySessions_Measure =

VAR PreDay =

PREVIOUSDAY ( Table1[Date] )

RETURN

LOOKUPVALUE ( Table1[SessionS], Table1[Date], PreDay )

Frequent Visitor
Thank you! I had tried the first method earlier and it didnt seem to work - but i will try it again

Can you add a measure when using the query? I was under the impression that it had to be done outside the query editor.
Microsoft Employee

Hi @afkl99,

We did it in the data view or report view. You also can try this formula.

```Column =
VAR currentDate = 'Table1'[Date]
VAR PreDate =
CALCULATE (
LASTDATE ( 'Table1'[Date] ),
FILTER ( 'Table1', 'Table1'[Date] < currentDate )
)
RETURN
IF (
ISBLANK ( PreDate ),
0,
DIVIDE (
'Table1'[Value],
LOOKUPVALUE ( 'Table1'[Value], Table1[Date], PreDate ),
0
)
)```

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Microsoft Employee

Hi @afkl99,

Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

Best Regards!
Dale

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