Flag Current Quarter's Rows By Comparing Previous Quarter's Set of Rows

[ramchoudhary]

Hi Team,

I need too flag a set of rows in current quarter based on the previous quarter's rows and name those rows with below mentioned conditons:

Below is the smaple data with desired output column named as "Result":

Date	Company ID	Security ID	Quarter	Result
01-01-2021	ABCD11	1001	0121
11-01-2021	ABCD12	1002	0121
21-01-2021	ABCD11	1003	0121
31-01-2021	ABCD12	1004	0121
10-02-2021	ABCD14	1002	0221
20-02-2021	ABCD11	1003	0221
02-03-2021	ABCD12	1004	0321
12-03-2021	ABCD11	1005	0321
22-03-2021	ABCD12	1006	0321
01-04-2021	ABCD13	1007	0421
11-04-2021	ABCD13	1008	0421
21-04-2021	ABCD12	1001	0421
01-05-2021	ABCD16	1009	0521
03-05-2021	ABCD16	1010	0521
05-05-2021	ABCD13	1001	0521
07-05-2021	ABCD15	1002	0521
09-05-2021	ABCD12	1002	0521
11-05-2021	ABCD11	1002	0521
13-05-2021	ABCD10	1002	0521
02-06-2021	ABCD10	1002	0621	Current
12-06-2021	ABCD13	1018	0621	New
21-06-2021	ABCD13	1019	0621	New
23-06-2021	ABCD13	1001	0621	Current
24-06-2021	ABCD12	1002	0621	Current
25-06-2021	ABCD11	1002	0621	Current
26-06-2021	ABCD10	1020	0621	New
27-06-2021	ABCD16		0621	Exit
28-06-2021	ABCD16		0621	Exit
29-06-2021	ABCD16		0621	Exit

Current Selection in Quarter Slicer is 0621 - now, we are trying to achieve the OUTPUT column Result as per below 3 condtions :

1. "New"      - Companies with Securities at selected quarter [Green rows] that didn't exist in Prior Quarter [Red rows]
2. "Current" - Companies with Securities at selected quarter [Green rows] that exsits in Prior Quarter [Red rows]
3. "Exit"       - Companies without Securities at selected quarter [Green rows] that had securities in Prior Quarter [Red rows]

Looking for a genuis to get this sorted.

Thanks,

Ram

[Ashish_Mathur]

You want to compare with the previous month (not with the previous quarter).  Am i correct?

[v-kkf-msft · Accepted solution]

Hi @ramchoudhary ,

From your data, you should be comparing the current month with the previous month's data. So please try the following formula:

Measure = 
VAR tab =
    SUMMARIZE ( 'Table', 'Table'[Company ID], 'Table'[Security ID] )
VAR PriorMonth =
    FORMAT ( EDATE ( MAX ( 'Table'[Date] ), -1 ), "mmyy" )
VAR P_M_tab =
    CALCULATETABLE (
        SUMMARIZE ( 'Table', 'Table'[Company ID], 'Table'[Security ID] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Quarter] = PriorMonth )
    )
VAR Current_ =
    COUNTROWS ( INTERSECT ( tab, P_M_tab ) )
VAR Exit =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        'Table'[Security ID] = BLANK (),
        INTERSECT (
            VALUES ( 'Table'[Company ID] ),
            CALCULATETABLE (
                VALUES ( 'Table'[Company ID] ),
                FILTER ( ALL ( 'Table' ), 'Table'[Quarter] = PriorMonth )
            )
        )
    )
VAR New =
    COUNTROWS ( EXCEPT ( tab, P_M_tab ) )
RETURN
    SWITCH ( TRUE (), Current_ > 0, "Current", Exit > 0, "Exit", New > 0, "New" )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vojtechsima]

HI, @ramchoudhary ,
Scratch my solution, this is absolutely awesome and will fill your needs.

@v-kkf-msft Thanks for this amazing solution.

[ramchoudhary]

Hi vojtechsima,

Thank you so much for your help here. 

I would love to have .pbix file you wokred. I need to check few things to see if things are dynamic based on the quarter selection.

Thanks!

[vojtechsima]

Hi, @ramchoudhary 
There's probably a way better way, how to do it, however, I managed to solve it like this:

I created a new calculated Table for the Previous Quarter (to make my life easier):

PreviousQuarter = 
var CurrentQuarter = MAX('Table'[Quarter])
var _previousQuarter = MAXX(FILTER('Table', 'Table'[Quarter] <> CurrentQuarter), 'Table'[Quarter])
return CALCULATETABLE(SELECTCOLUMNS('Table', "UniqueValue", 'Table'[UniqueValue], "Quarter", 'Table'[Quarter], "Company" , 'Table'[Company ID], "Security", 'Table'[Security ID], "IsBlank", IF(ISBLANK('Table'[Security ID])=TRUE(), TRUE(), FALSE())) , 'Table'[Quarter] = _previousQuarter)

 Then in the original Table, I created a new calculated column that takes Company and Security and put them together so I can get some Unique value:

UniqueValue = CONCATENATE('Table'[Company ID],'Table'[Security ID])

Then Finally I created this measure that creates the output, you wanted (Tho I am not sure about the Exit, please try it if it works for you):

ResultNew = 

var currentCompany = 'Table'[Company ID]
var currentSecurityID = 'Table'[Security ID]
var Row_currentQuarter = 'Table'[Quarter]
var UniqueValue = 'Table'[UniqueValue]
var CoresspondingBlankCompany = MAXX(FILTER('PreviousQuarter', currentCompany = 'PreviousQuarter'[Company] && 'PreviousQuarter'[IsBlank] = True()),'PreviousQuarter'[Company])

var currentQuarter = MAX('Table'[Quarter])

var CurrentCheck = IF('Table'[Quarter] = currentQuarter && UniqueValue IN VALUES('PreviousQuarter'[UniqueValue]), "Current")
var NewCheck = IF('Table'[Quarter] = currentQuarter && NOT(UniqueValue IN VALUES('PreviousQuarter'[UniqueValue])), "New")
var ExitCheck = IF('Table'[Quarter] = currentQuarter && ISBLANK('Table'[Security ID])= TRUE() && currentCompany <> CoresspondingBlankCompany, "Exit")

var Result = SWITCH(FALSE(),
ISBLANK(CurrentCheck), "Current",
ISBLANK(ExitCheck), "Exit",
ISBLANK(NewCheck), "New")


return Result

I hope it does work for you.