Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

For Each Formula - Calculated Column

Hi

 

I am trying to count the days between two stages for each MR No. What is the best way to do this in Power BI? In excel I would use =IF(B3=B2, (DAYS(A3,A2)),(0)).

Many thanks!

 

 

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Hi Anonymous

     

    How about this one?

     

    Date_Diff =
VAR NextAuditDate =
    CALCULATE (
        VALUES ( TableName[Audit Date] ),
        FILTER (
            ALL ( TableName ),
            TableName[MR No.] = EARLIER ( TableName[MR No.] )
                && TableName[Date RANK]
                    = EARLIER ( TableName[Date RANK] ) + 1
        )
    )
RETURN
    DATEDIFF ( TableName[Audit Date], NextAuditDate, DAY )

  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi Anonymous,

     

    This calculated column formula works

     

    =if(ISBLANK(CALCULATE(MIN(Data[Audit Date]),FILTER(Data,Data[MR No.]=EARLIER(Data[MR No.])&&Data[Audit Date]>EARLIER(Data[Audit Date])))),BLANK(),CALCULATE(MIN(Data[Audit Date]),FILTER(Data,Data[MR No.]=EARLIER(Data[MR No.])&&Data[Audit Date]>EARLIER(Data[Audit Date])))-[Audit Date])

     

     

4 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi Anonymous,

     

    This calculated column formula works

     

    =if(ISBLANK(CALCULATE(MIN(Data[Audit Date]),FILTER(Data,Data[MR No.]=EARLIER(Data[MR No.])&&Data[Audit Date]>EARLIER(Data[Audit Date])))),BLANK(),CALCULATE(MIN(Data[Audit Date]),FILTER(Data,Data[MR No.]=EARLIER(Data[MR No.])&&Data[Audit Date]>EARLIER(Data[Audit Date])))-[Audit Date])

     

     

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    Hi Anonymous

     

    Try this solution

     

    First add a calculated column "DATE RANK"

    Date RANK=
RANKX (
    FILTER ( TableName, TableName[MR No.] = EARLIER ( TableName[MR No.] ) ),
    TableName[Audit Date],
    TableName[Audit Date],
    ASC,
    DENSE
)

     

     

    Then you can compute the Days Difference between each successive audit dates for each MR No using this formula

     

    Date Difference=
VAR PreviousAuditDate =
    CALCULATE (
        VALUES ( TableName[Audit Date] ),
        FILTER (
            ALL ( TableName ),
            TableName[MR No.] = EARLIER ( TableName[MR No.] )
                && TableName[Date RANK]
                    = EARLIER ( TableName[Date RANK] ) - 1
        )
    )
RETURN
    DATEDIFF ( PreviousAuditDate, TableName[Audit Date], DAY )

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hello and many thanks for this :)! Just one more thing - I need to show the "date difference" count on the preceeding audit date to show how long each stage took before it moved to the next one. So, the 5 days difference on line 2 should move to line 1 and so on.  

       

      Thank you for your help.

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        8 years ago

        Hi Anonymous

         

        How about this one?

         

        Date_Diff =
VAR NextAuditDate =
    CALCULATE (
        VALUES ( TableName[Audit Date] ),
        FILTER (
            ALL ( TableName ),
            TableName[MR No.] = EARLIER ( TableName[MR No.] )
                && TableName[Date RANK]
                    = EARLIER ( TableName[Date RANK] ) + 1
        )
    )
RETURN
    DATEDIFF ( TableName[Audit Date], NextAuditDate, DAY )