Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  Datediff in same column

    Posted Feb 01, 2021 11:14 AM
    Edited by julien rouleau Feb 02, 2021 11:40 AM

    Hi everyOne

    I have tried several solutions to solve this problem.

    See explanation in "comment" column and expected result in "Expected value" column :

    I 've spent hours on this formula but this is not the result i expect.

    Could you please have a look on my problem please ?

    Thanks

     Expected value =
    VAR MaxRank =
        CALCULATE ( MAX ( DT[Item Rank] ), ALLEXCEPT ( DT, DT[NUMBEREDF] ) )
    VAR temp =
        TOPN (
            1,
            FILTER (
                DT,
                DT[NUMBEREDF] = EARLIER ( DT[NUMBEREDF] )
                    && DT[DATEEDF] < EARLIER ( DT[DATEEDF] )
            ),
            DT[NUMBEREDF], DESC
        )
    RETURN
        IF (
            DT[ID group Rank] = 1
                && DT[ID group Rank] <> MaxRank,
            0,
            IF (
                DT[ID group Rank] = MaxRank,
                DATEDIFF ( DT[Change date], DT[Close id date], DAY ),
                DATEDIFF ( MAXX ( temp, DT[Change date] ), DT[Change date], DAY )
            )
        )


    ------------------------------
    julien
    ------------------------------


  • 2.  RE: Datediff in same column

    Posted Jan 15, 2022 02:31 PM

    Steps to populate differrence between two immediate rows in same column to another column(A2-A1)

    step 1-create two index column from Add column section ie one starting with 0 with column name as Index and another index column starting with 1 with name Index.1

    Step2-Add a new column with respective name and use the below formula and enter

    var diff= Table column name-CALCULATE(SUM(Table column name),FILTER('tablename','tablename'[Index.1]=EARLIER('insights-logs-connection'[Index]))) return IF (diff=VALUE(Table column name),0,diff)

    Table column name will be like 'table name'[column name] choose automaticaly from power BI list dropdwon

    Table name will be like 'table name' choose automaticaly from power BI list dropdown

    ------------------------------
    Jag kris
    ceo
    ------------------------------