Compare a list of events from 2 different dates and find the ones that existed or not in both

[WorkHard]

I have a list of events. Once a week, I backup the list and I merge all previous backups in the same list.

I end up with a master list that contains the entire list of events including previous backups.

I'd like to compare the latest backup with the previous backup and see:

• which events exist in both backups;
• which events exist only in the previous backup
• which events exist only in the current (latest) backup.

Is it possible to create a calculated column using DAX to achieve the result? A measure could potentially work but I can't use the measure in slicers or filters.

[Jihwan_Kim]

Hi, @WorkHard 

I tried to create a sample pbix file based on your explanation.

Please check the below picture and the sample pbix file's link down below, whether it suits your case.

Presence =

VAR currentweeknumber =

WEEKNUM ( TODAY (), 1 )

VAR currentweekevent =

CALCULATETABLE (

VALUES ( Events[EventID] ),

FILTER ( ALL ( Dates ), Dates[Week of Year] = currentweeknumber )

)

VAR previousevent =

CALCULATETABLE (

VALUES ( Events[EventID] ),

FILTER ( ALL ( Dates ), Dates[Week of Year] < currentweeknumber )

)

VAR latestonlylist =

EXCEPT ( currentweekevent, previousevent )

VAR bothlist =

INTERSECT ( currentweekevent, previousevent )

VAR previousonlylist =

EXCEPT ( previousevent, currentweekevent )

VAR currentevent =

MAX ( Events[EventID] )

RETURN

SWITCH (

TRUE (),

currentevent IN bothlist, "Both", // this argument has to be the first otherwise the result will not be correct

currentevent IN latestonlylist, "Latest Only",

currentevent IN previousonlylist, "Previous Only",

"Check again"

)

https://www.dropbox.com/s/t9pzjqwhat1sbof/workhard.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[WorkHard]

Thanks @Jihwan_Kim ,

What would it take to turn this into a calculated column? 
I'm trying to use this measure as a chart legend but because it's a measure I can't add it to the legend or a slicer.

[Jihwan_Kim · Accepted solution]

Hi, @WorkHard 

Thank you for your feedback.

Please check the below measure that is for creating a column.

Presence Column =

VAR currentweeknumber =

WEEKNUM ( TODAY (), 1 )

VAR currentweekevent =

CALCULATETABLE (

values( Events[EventID] ),

FILTER ( ALL ( Events ), WEEKNUM(Events[Date]) = currentweeknumber )

)

VAR previousevent =

CALCULATETABLE (

values( Events[EventID] ),

FILTER ( ALL ( Events ), WEEKNUM(Events[Date]) < currentweeknumber )

)

VAR latestonlylist =

EXCEPT ( currentweekevent, previousevent )

VAR bothlist =

INTERSECT ( currentweekevent, previousevent )

VAR previousonlylist =

EXCEPT ( previousevent, currentweekevent )

VAR currentevent =

Events[EventID]

RETURN

SWITCH (

TRUE (),

currentevent IN bothlist, "Both", // this argument has to be the first otherwise the result will not be correct

currentevent IN latestonlylist, "Latest Only",

currentevent IN previousonlylist, "Previous Only",

"Check again"

)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[WorkHard]

Ah. That's simple.

I see one more issue. I just want to compare the results from this week vs last week.

So I added currentweeknumber-1:

VAR previousevent =
CALCULATETABLE (
values( Events[EventID] ),
FILTER ( ALL ( Events ), WEEKNUM(Events[Date]) = currentweeknumber-1 )
)

But, for some reason. Event 62 from April 13 and Event 63 from April 14 still show up as "Previous Only" when they are actually more than 2 weeks ago.
Any reason why they show up like that instead of "Check again"?

[Jihwan_Kim]

Hi @WorkHard 

Thank you for your feedback.

I think the reason is that event 62 and 63 is also in 18th and 19th.