Forum Discussion
Opposite Intersection
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.
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
)