For Each Formula - Calculated Column

[Anonymous]

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 · Accepted solution]

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 · Accepted solution]

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]

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]

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 · Accepted solution]

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 )