Forum Discussion

Bastille92's avatar
Bastille92
Frequent Visitor
8 years ago

Date Slicer (User Addition + User Removals)

 

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
        
IDName IDName IDName
00001Bryan 00001Bryan 00001Bryan
00002John 00002John 00002John
00003Mac 00003Mac 00003Mac
00004Annie 00004Annie 00005Henry
   00005Henry 00007Paul
   00006John 00008Jack
   00007Paul 00009Liam
   00008Jack 00010Hank
   00009Liam   

 

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

 

Combined File
DateIDName
28/03/201800001Bryan
28/03/201800002John
28/03/201800003Mac
28/03/201800004Annie
04/04/201800001Bryan
04/04/201800002John
04/04/201800003Mac
04/04/201800004Annie
04/04/201800005Henry
04/04/201800006John
04/04/201800007Paul
04/04/201800008Jack
04/04/201800009Liam
11/04/201800001Bryan
11/04/201800002John
11/04/201800003Mac
11/04/201800005Henry
11/04/201800007Paul
11/04/201800008Jack
11/04/201800009Liam
11/04/201800010Hank

 

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 AddedIDName
04/04/201800005Henry
04/04/201800006John
04/04/201800007Paul
04/04/201800008Jack
04/04/201800009Liam
   

Removed

   

 

Or this Example

 

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

Added

 

Date AddedIDName
04/04/201800005Henry
04/04/201800006John
04/04/201800007Paul
04/04/201800008Jack
04/04/201800009Liam
11/04/201800010Hank

 

Removed

Date RemovedIDName
11/04/201800004Annie
11/04/201800006John

 

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.

Need Help

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      Bastille92
      Frequent Visitor
      8 years ago
      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


    • Bastille92's avatar
      Bastille92
      Frequent Visitor
      8 years ago

      So right now the scenario i am having is this.

       

      Table 1 Table 2
      IDDate  IDMin DateMax Date
      00000115/01/2018  00000115/01/201819/01/2018
      00000119/01/2018  00000208/01/201822/03/2018
      00000222/02/2018  00000318/01/201801/02/2018
      00000208/01/2018     
      00000211/01/2018     
      00000222/03/2018     
      00000318/01/2018     
      00000322/01/2018     
      00000301/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's avatar
        Bastille92
        Frequent Visitor
        8 years ago

        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"