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 ...
Need Help
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

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"

    • Bastille92's avatar
      Bastille92
      Frequent Visitor
      8 years ago

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

        Bump,

         

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

         

        Regards