Forum Discussion

Frequent Visitor

9 years ago

Solved

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...

Need Help

CahabaData

Memorable Member

9 years ago

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

Frequent Visitor

9 years ago

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
Microsoft Employee
9 years ago
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
  Microsoft Employee
  8 years ago
  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