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
mail2vjj's avatar
mail2vjj
Helper III
8 years ago

I have reached a partial solution, but it is only working if I choose a date on the slicer.

For result one this is the forumla I have used.

 

Result 1 = EXCEPT(
CALCULATETABLE(VALUES(Sheet1[Name]), FILTER(ALLEXCEPT(Sheet1, Sheet1[Name]), Sheet1[Date] = SELECTEDVALUE(Sheet1[Date]))),
CALCULATETABLE(VALUES(Sheet1[Name]), FILTER(ALLEXCEPT(Sheet1, Sheet1[Name]), Sheet1[Date] < SELECTEDVALUE(Sheet1[Date])))
)

 

For Result 2 I just filpped the 'less than' and 'equal to' signs to change the tables.

 

Result 2 = EXCEPT(
CALCULATETABLE(VALUES(Sheet1[Name]), FILTER(ALLEXCEPT(Sheet1, Sheet1[Name]), Sheet1[Date] < SELECTEDVALUE(Sheet1[Date]))),
CALCULATETABLE(VALUES(Sheet1[Name]), FILTER(ALLEXCEPT(Sheet1, Sheet1[Name]), Sheet1[Date] = SELECTEDVALUE(Sheet1[Date])))
)

 

However the problem I am facing here is that, since Name C has occured previously, it is not giving me the desired result, when is reoccurs on 6th Jan 2018.

Also, probably since I am using SELECTEDVALUES, the Matrix visual gives me an error until I select a date in the the slicer.

 

If anyone can correct my mistake or bulid on the formula that I am using, it would be of great help.

 

Thank you,

 

Vishesh Jain.

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