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])

     

     

Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity 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
    Icon for Community Champion rankCommunity 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 )