topic Re: calculate from second row in DAX Commands and Tips

calculate from second row

Anonymous — Mon, 11 May 2020 23:07:57 GMT

Hello everyone,

I need to calculate a formula taking into account data from the second row.

I show you an example in excel:

I have a filter in Power BI by date, which will select the numbers. Then I must apply that formula from the second row.

This means: data must be selected from the next day the filter was selected. In the example above, the user would select in the date slicer from 7 to 14 of May, so data must be calculated from 8 to 14th of May.

I calculated an index column in Power Query. And a "first row" measure which selects the index for the first row:

First row = CALCULATE(MIN('Return'[Índice]); FILTER('Return'; LASTNONBLANK('Return'[Índice]; true())))

Then I calculated a column:

Column = CALCULATE(SUM('Return'[Return])+1; ALLSELECTED('Return'[Date])

Then I tried to calculate that product:

Measure = CALCULATE( PRODUCT('Return'[Columna])-1)

I am not able to include a filter to ignore the first row and start calculate from the second one. I tried "earlier", all kinds of filters, I just can't get to it.

Thank you in advance! Any advice is very much appreciated 🙂

Re: calculate from second row

mahoneypat — Tue, 12 May 2020 01:01:21 GMT

I loaded your excel data into a table called Second, and made a table visual and this measure, and put it on a card.

StartWithSecond = VAR filteredtable = FILTER('Second', 'Second'[Date]>min('Second'[Date]))

return PRODUCTX(filteredtable, 1+'Second'[Value])-1

If this solution works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat

Re: calculate from second row

Anonymous — Tue, 12 May 2020 06:45:14 GMT

Thank you so much mahoneypat !!

I was really complicating myself with collumns and so on... It works wonderfully!! Thanks again!