Date Slicer (User Addition + User Removals)

[Bastille92]

Hi there, I would like to request some help on this.....

I have a need to use the Timeline slicer which usually has a Start Date and an End Date

Imagine i populate dataset of historical data based on multiple excel file, 

As Such

File:	28/03/2018		File:	04/04/2018		File:	11/04/2018
ID	Name		ID	Name		ID	Name
00001	Bryan		00001	Bryan		00001	Bryan
00002	John		00002	John		00002	John
00003	Mac		00003	Mac		00003	Mac
00004	Annie		00004	Annie		00005	Henry
			00005	Henry		00007	Paul
			00006	John		00008	Jack
			00007	Paul		00009	Liam
			00008	Jack		00010	Hank
			00009	Liam

It Then Gets combined into one Combined Dataset for which the dashboard can pull data for historical purposes like follow.

Combined File
Date	ID	Name
28/03/2018	00001	Bryan
28/03/2018	00002	John
28/03/2018	00003	Mac
28/03/2018	00004	Annie
04/04/2018	00001	Bryan
04/04/2018	00002	John
04/04/2018	00003	Mac
04/04/2018	00004	Annie
04/04/2018	00005	Henry
04/04/2018	00006	John
04/04/2018	00007	Paul
04/04/2018	00008	Jack
04/04/2018	00009	Liam
11/04/2018	00001	Bryan
11/04/2018	00002	John
11/04/2018	00003	Mac
11/04/2018	00005	Henry
11/04/2018	00007	Paul
11/04/2018	00008	Jack
11/04/2018	00009	Liam
11/04/2018	00010	Hank

What i need to do is Using that Timeline Slider, create a list of the ID's that were Added between Start date and End date

and those that have been removed from Start Date to End Date.

Results should be as such.

Start Date: 28/03/2018 End Date: 04/04/2018

Added

Date Added	ID	Name
04/04/2018	00005	Henry
04/04/2018	00006	John
04/04/2018	00007	Paul
04/04/2018	00008	Jack
04/04/2018	00009	Liam

Removed

Or this Example

Start Date: 28/03/2018 End Date: 11/04/2018

Added

Date Added	ID	Name
04/04/2018	00005	Henry
04/04/2018	00006	John
04/04/2018	00007	Paul
04/04/2018	00008	Jack
04/04/2018	00009	Liam
11/04/2018	00010	Hank

Removed

Date Removed	ID	Name
11/04/2018	00004	Annie
11/04/2018	00006	John

Notice that Date Added is the date of first occurence within the time selected in the Timescale Slicer

and the Date Removed is the date between the date of first occurence and the last date selected in the Timescale Slicer.

Would appreciate if someone can give some advise on how to do this.

[v-yuezhe-msft]

@Bastille92,

To check added rows, you can create the following columns in the combined table.

PreID = CALCULATE(FIRSTNONBLANK(Table1[ID],1),FILTER(Table1,Table1[ID]=EARLIER(Table1[ID]) && Table1[Date]<EARLIER(Table1[Date])))

checkaddrow = IF(ISBLANK(Table1[PreID]) && Table1[Date]>MIN(Table1[Date]),1,0)

Then drag checkaddrow column to visual level filter and set its value to 1.


To check removed rows, the method that I can think of is to create new table listing missing rows between tables using Except() function, there is a similar thread for your reference. 

Regards,
Lydia

[Bastille92]

So right now the scenario i am having is this.

Table 1		Table 2
ID	Date			ID	Min Date	Max Date
000001	15/01/2018			000001	15/01/2018	19/01/2018
000001	19/01/2018			000002	08/01/2018	22/03/2018
000002	22/02/2018			000003	18/01/2018	01/02/2018
000002	08/01/2018
000002	11/01/2018
000002	22/03/2018
000003	18/01/2018
000003	22/01/2018
000003	01/02/2018

I need a Fomula for Calculated Columns "Min Date" and "Max Date" by looking up table 2 ID to seach the min and max date by looking up to Table 1 ID.

[Bastille92]

Okay Now i managed something like this.

Where

Add Date = Calculate(MIN('Snap Historical'[Snap Date]),FILTER(ALL('Snap Historical'[C-Number]),'Snap Historical'[C-Number]='Table'[C-Number]))

Removed Date = IF(Calculate(MAX('Snap Historical'[Snap Date]),FILTER(ALL('Snap Historical'[C-Number]),'Snap Historical'[C-Number]='Table'[C-Number]))=MAX('Snap Historical'[Snap Date]),Blank(),Calculate(MAX('Snap Historical'[Snap Date]),FILTER(ALL('Snap Historical'[C-Number]),'Snap Historical'[C-Number]='Table'[C-Number])))

However, now i need assistance to modify the "Add Date" so that the Returned "Add Date" will Always be getting the Minimum Date that is Larger than " Removed Date"

[Bastille92]

Add Date = Calculate(MIN('Snap Historical'[Snap Date]),FILTER(ALL('Snap Historical'[C-Number]),'Snap Historical'[C-Number]='Table'[C-Number]),FILTER(ALL('Snap Historical'[Snap Date]),'Snap Historical'[Snap Date]>='Table'[Removed Date]))

Is this right??

Can i be using >= or it needs to be in a specific format like on Excel where its >=&'Table'[Removed Date]

[Bastille92]

Bump,

still awiting on a resoponse to see if the formula for hte previous post for the >= works or it has to be >=&

Regards

[v-yuezhe-msft]

@Bastille92，

What are the tables(Snap Historical, Table) do you refer to in your DAX? And what is the expected result based on the new Table 1 and Table 2?

Regards,
Lydia

[Bastille92]

Hi Lydia,

Thanks for the response

Instead of using calculated column on the existing data set

Do you know any way to create an entirely new table that automatically fills in by referencing the combined file, the ID numbers as the first column by distinct IDs so no duplicate ID

If thats doable, dunno by dax query or by calculated column for the new table...

If thats do-able then using that new table we can have a Add Date and Remove Date column where add date is lookup the ID to find of the minimum date where the ID first occured