Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

calculate from second row

Hello everyone,

 

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

I show you an example in excel:

 

test.PNG

 

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 🙂 
1 ACCEPTED SOLUTION
mahoneypat
Employee
Employee

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
 
second.png
If this solution works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.
Regards,
Pat




Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

2 REPLIES 2
mahoneypat
Employee
Employee

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
 
second.png
If this solution works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.
Regards,
Pat




Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

Thank you so much @mahoneypat !!

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors