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
    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"

      • 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]