Forum Discussion
Flagging New Accounts Based on Prior Ordering
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 |
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.
3 Replies
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 , 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()
)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.
