Forum Discussion

mail2vjj's avatar
mail2vjj
Helper III
8 years ago

Opposite Intersection

I have a table which looks like the following:   Date Name Holding Change Category 01-01-18 A 10 10 Founding 01-01-18 B 10 10 Founding 01-01-18 C 10 10 Public 02-01-1...
Need Help
Show and Tell
Tips and Tricks
Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi mail2vjj,

 

You can use below measure to get name list which not exist in last date.

Not exist In Previous = 
VAR _currDate =
    SELECTEDVALUE ( Table1[Date] )
VAR _prevDate =
    MAXX ( FILTER ( ALLSELECTED ( Table1 ), [Date] < _currDate ), [Date] )
VAR _current =
    CALCULATETABLE (
        VALUES ( Table1[Name] ),
        FILTER ( ALLSELECTED ( Table1 ), Table1[Date] = _currDate )
    )
VAR _previous =
    CALCULATETABLE (
        VALUES ( Table1[Name] ),
        FILTER ( ALLSELECTED ( Table1 ), Table1[Date] = _prevDate )
    )
RETURN
    IF (
        _prevDate <> BLANK (),
        CONCATENATEX ( EXCEPT ( _current, _previous ), [Name], "," )
    )

 

Regards,

Xiaoxin Sheng

mail2vjj's avatar
mail2vjj
Helper III
8 years ago

AnonymousThank you for your response and sorry for not being clear about my question.

 

Your solution almost works for me, however I need the result as a list rather than seperated by commas.

 

So is there anything else that we can try to get the desired result.

 

 Name
Date 
05-01-18D
 E
 F
06-01-18C
 A
 B


This is the sort of result I am looking for as my table will be consisting of thousands of names, so getting a list seperated by commas, will not be an optimal solution for me. 


I am trying to use the Matrix Visual as it will combine the date for me and give me the names in the row for every date.

 

This is just a dummy example of the result, actual result will consist of names in every row.

 

Again, thank you for all your help and sorry for not being clear enough in the first place.

 

Vishesh Jain