Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 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...
Need Help
  • Fowmy's avatar
    Fowmy
    6 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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 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
      Icon for Super User rankSuper User
      6 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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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
        2644547335/20/2018
        23103505/27/2018
        23102306/3/2018
        23101106/10/2018
        2310 -10 6/15/2018

         

        It really made my day. Thanks :)...

         

        Just one nag on how to equate negative ones to blank values...Your inputs please... 🙂