Flagging New Accounts Based on Prior Ordering

[mromberg]

Failry new user to PowerBI and need help with a calculation.  I'm trying to flag new accounts for the current quarter.  First qualfiier is order in Current Quarter>0.  Second qualifier is no orders IN THE SAME TERRITORY, for the Prior Quarter.  With these two criteria met this would qualify as a New Current Qtr Account. If an account has an order in the same territory for both Prior Quarter and Current Quarter this is NOT a new account.  Need help with the 'New Current Qtr Account' column.  Data example below.  Thanks!

Account #	Territory	Time Period	Order Count	New Current Qtr Account
123	San Diego	Current Quarter	1	Y
123	Los Angeles	Prior Quarter	1
321	Miami	Current Quarter	1	N
321	Miami	Prior Quarter	1
543	Kansas City	Current Quarter	1	Y

[v-xiaotang · Accepted solution]

Hi @mromberg 

Try this, and please check the sample file attached below

Column = 
VAR _isnew =
    IF (
        CALCULATE (
            COUNTROWS ( 'Table' ),
            FILTER (
                'Table',
                'Table'[Account #] = EARLIER ( 'Table'[Account #] )
                    && 'Table'[Territory] = EARLIER ( 'Table'[Territory] )
                    && 'Table'[Time Period] = "Prior Quarter"
            )
        ) > 0,
        "N",
        "Y"
    )
RETURN
    IF ( 'Table'[Time Period] = "Current Quarter", _isnew, BLANK () )

Best Regards,

Community Support Team _Tang

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

[mromberg]

Oh so close.!  However, I did not represent my data correctly in the example I gave.  There is another scenario where account has current orders, no orders last quarter and HAS orders two quarters ago.  In this case we would want to call this a new account.  See Seattle.

[amitchandak]

@mromberg , a new column

New Column =
var _cnt = countx(filter(Table, [Account#] = earlier([Account#]) && [Territory] = earlier([Territory]) ), [Account#])
return
Switch(True(),
[Time Period] = "Current Quarter" && _cnt >1, "N",
[Time Period] = "Current Quarter" && _cnt = 1,"Y",
blank()
)