Dividing rows in table

[afkl99]

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! 

[CahabaData · Accepted solution]

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 )

[afkl99]

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.  

[v-jiascu-msft]

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

[v-jiascu-msft]

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