Forum Discussion
EARLIER problem while framing a formula for previous row field
Anonymous
Add this as a new column in your table:TB = VAR __First = MIN(Query1[Total Scope (Pts)]) VAR __FirstDate = MIN(Query1[ Trending Date]) return IF( [ Trending Date] = __FirstDate , __First , __First - COUNTROWS( FILTER( Query1, Query1[ Trending Date] < EARLIER(Query1[ Trending Date]) ) ) * 12 )________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
Hi,
Thanks for the reply. Here is a sample data in tabular format.
| Total Scope (Pts) | Actual Burndown (Pts) | Theoretical Burndown (Pts) | Dev Complete (Pts) | Trending Date |
| 203 | 203 | 203 | 203 | 2/18/2018 |
| 203 | 203 | 191 | 203 | 2/25/2018 |
| 203 | 203 | 178 | 151 | 3/4/2018 |
| 227 | 208 | 166 | 156 | 3/11/2018 |
What I want to calculate is the 3rd Column which is feeded with correct data as above in Excel. The same result I want to achieve in Power BI.
Here, Theoretical Burndown (Pts) = Previous Theoretical Burndown (Pts) - 12, from Second Row onwards.
For First row, this formula is: Theoretical Burndown (Pts) = Total Scope (Pts), from First Row.
And this is the formula I am applying...
Super UserAnonymous
Add this as a new column in your table:
TB =
VAR __First = MIN(Query1[Total Scope (Pts)])
VAR __FirstDate = MIN(Query1[ Trending Date])
return
IF( [ Trending Date] = __FirstDate , __First ,
__First -
COUNTROWS(
FILTER(
Query1,
Query1[ Trending Date] < EARLIER(Query1[ Trending Date])
)
) * 12
)
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
Thank you so much for help. That really did work but with just one exception. And that is the result which is coming in -ve for some values. In that case, it should either equate to zero or null for no value or a blank value.
For ex.:
Total Scope (Pts) Actual Burndown (Pts) Theoretical Burndown (Pts) Dev Complete (Pts) Trending Date 264 45 47 33 5/20/2018 231 0 35 0 5/27/2018 231 0 23 0 6/3/2018 231 0 11 0 6/10/2018 231 0 -1 0 6/15/2018 It really made my day. Thanks :)...
Just one nag on how to equate negative ones to blank values...Your inputs please... 🙂