EARLIER problem while framing a formula for previous row field

[Anonymous]

Hi All,

I am having a problem while framing a formula for a row field in a column.

The following picture shows what I wanted from the fields. I tried EARLIER function but it does not come out to be like that.

Any help would be appreciated.

EARLIER Issue

For ex., in the second row, theoretical burndown = First Row Theoretical Burndown - Theoretical Velocity.

For First row, theoretical burndown = Total Scope Pts.

For Third row, theoretical burndown = Second Row Theoretical Burndown -Theoretical Velocity(constant value which never change)

and so on......

Please help.

Thanks,

Nitin

[Fowmy]

@Anonymous 

Can you share some sample data with all the columns you stated in the explanation?

____________________________________
How to paste sample data with your question?
How to get your questions answered quickly?

_____________________________________
Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn

[Anonymous]

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

"Theo Burndown = VAR minPath =

CALCULATE (

SUM ( 'Query1 on State'[Total Scope (Pts)] ),

FILTER (

ALLSELECTED ( 'Query1 on State' ),

'Query1 on State'[Total Scope (Pts)] = EARLIER ( 'Query1 on State'[Total Scope (Pts)])

)

)

RETURN

LOOKUPVALUE (

'Query1 on State'[Total Scope (Pts)],

'Query1 on State'[DateValue], minPath,

MIN ( 'Query1 on State'[Total Scope (Pts)] )"

This is giving 203 for every row in that column and that is not the correct one.

Please help.

[Fowmy · Accepted solution]

@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 🙂

YouTube  LinkedIn

[Anonymous]

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