Help with code for looking at previous service order type

[GunnerJ]

I have some code the works most of the time, but I'm a bit confused when it seems to lose it's logic. 

The custom column "Last SO" is supposed to show the BI_SO_TYPE_CD of the service orders that came before but only when they switch. In the picture below you can see the "ADD" service orders were first and all of the "CUTOFF" service orders follow and have "ADD" as the Last SO. That is how it's supposed to work. However at the very bottom the Last SO shows "DISC" when it should be "CUTOFF". I really can't explain why this is and could use some help. 

Here is the dax for the custom column. Its trying to look to make sure the account is the same, as well as the BI_SRV_MAP_LOC. It then returns the last SO_TYPE that wasn't the current SO_TYPE. 

Last SO =

CALCULATE(lASTNONBLANK('SO Attrition'[BI_SO_TYPE_CD],1),FILTER('SO Attrition', 'SO Attrition'[BI_ACCT] = EARLIER('SO Attrition'[BI_ACCT]) && 'SO Attrition'[NEEDED_DT] < EARLIER('SO Attrition'[NEEDED_DT]) && 'SO Attrition'[SRV_MAP_LOC] = EARLIER('SO Attrition'[SRV_MAP_LOC])&& 'SO Attrition'[BI_SO_TYPE_CD] <> EARLIER('SO Attrition'[BI_SO_TYPE_CD])))

Link. Screenshot is from "page 4"

https://www.dropbox.com/s/qfoxuxxzekvv34k/Test%20Attrition%20File.pbix?dl=0

[v-zhangti · Accepted solution]

Hi, @GunnerJ 

There will be some logical loops in your formula, resulting in wrong results. I calculate the last date first, and then output the corresponding BI_SO_TYPE_CD.

Add two calculated columns:

Last SO date = 
CALCULATE (
   MAX('SO Attrition'[NEEDED_DT]),
    FILTER (
        'SO Attrition',
        'SO Attrition'[BI_ACCT] = EARLIER ( 'SO Attrition'[BI_ACCT] )
            && 'SO Attrition'[NEEDED_DT] < EARLIER ( 'SO Attrition'[NEEDED_DT] )
            && 'SO Attrition'[SRV_MAP_LOC] = EARLIER ( 'SO Attrition'[SRV_MAP_LOC] )
             &&NOT(ISBLANK('SO Attrition'[BI_SO_TYPE_CD])
    )
))

Last SO New = 
CALCULATE (
    MAX ( 'SO Attrition'[BI_SO_TYPE_CD] ),
    FILTER (
        'SO Attrition' ,
        [NEEDED_DT] = EARLIER ( 'SO Attrition'[Last SO date] )
            && 'SO Attrition'[BI_ACCT] = EARLIER ( 'SO Attrition'[BI_ACCT] )
            && 'SO Attrition'[SRV_MAP_LOC] = EARLIER ( 'SO Attrition'[SRV_MAP_LOC] )
    )
)

 The new result is shown in the figure.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.