cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GunnerJ
Helper V
Helper V

Help with code for looking at previous service order type

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. 

 

GunnerJ_0-1638214757173.png

 

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"

 

1 ACCEPTED SOLUTION
v-zhangti
Community Support
Community Support

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.

vzhangti_0-1638432187286.png

 

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.

 

 

View solution in original post

1 REPLY 1
v-zhangti
Community Support
Community Support

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.

vzhangti_0-1638432187286.png

 

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.

 

 

View solution in original post

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors