Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

EARLIER problem while framing a formula for previous row field

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

Need Help
  • Fowmy's avatar
    Fowmy
    5 years ago

    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

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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
      2032032032032/18/2018
      2032031912032/25/2018
      2032031781513/4/2018
      2272081661563/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's avatar
        Fowmy
        Super User
        5 years ago

        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