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

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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