Forum Discussion

mail2vjj's avatar
mail2vjj
Helper III
8 years ago

Opposite Intersection

I have a table which looks like the following:

 

DateNameHoldingChangeCategory
01-01-18A1010Founding
01-01-18B1010Founding
01-01-18C1010Public
02-01-18A2010Founding
02-01-18B3020Founding
02-01-18C4030Public
03-01-18A10-10Founding
03-01-18B5020Founding
03-01-18C30-10Public
04-01-18a10090Founding
04-01-18b10050Founding
04-01-18c10070Public
05-01-18a200100Founding
05-01-18B50-50Founding
05-01-18D1251000Public
06-01-18a100-100Founding
06-01-18b200150Founding
06-01-18c100100Public
06-01-18d75-50Public

 

I want to find 2 things which are the Opposite Intersection of the names which are not present in the previous date and present in the current date and vice versa.

 

Result 1:

For example:

Name D is present in on 5th Jan 2018 but was not present on 4th Jan 2018.

Name C is present on 6th Jan 2018 but was not present on 5th Jan 2018.

So the table will look like this, with the list of all the names for every particular date.

 Names
Date 
05-01-18D
06-01-18C

 

Result 2:

For example:

Name C is present on 4th Jan 2018 but not present on 5th Jan 2018.

 Names
Date 
05-01-18C

So the table will look like this, with the list of all the names for every particular date.

 

Any help will be appreciated.

 

Thank you,

 

Vishesh Jain

Need Help
Show and Tell
Tips and Tricks

9 Replies

  • 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.

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      8 years ago

      mail2vjj

       

       

      Try this. From Modelling Tab>>> NEW TABLE

       

      New Table =
FILTER (
    SUMMARIZE (
        TableName,
        TableName[Date],
        TableName[Name],
        "Missing", PREVIOUSDAY ( TableName[Date] )
            = CALCULATE (
                MAX ( TableName[Date] ),
                TableName[Date] < EARLIER ( TableName[Date] )
            )
    ),
    TableName[Date] <> DATE ( 2018, 1, 1 )
        && [Missing] = FALSE
)
    • 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