Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Reference a second table to calculate if in a date range

Hi

 

I am needing help to get this working.  If i hard code the Start Date and End Date in it works, but i would like it to reference a different table "Date Table", that will be updated each month. 

 

I would also like to add an error checking, so it returns "Prior Period" also.

 

Thank you in advance.


if
[Set Up Date] >= #"Date Table",[Start Date] and [Set Up Date] <= #"Date Table",[End Date] or
[Senstive Field Change] >= #"Date Table",[Start Date] and [Senstive Field Change] <= #"Date Table",[End Date]

then "Current Month"

else "Prior Period"

Need Help

3 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

    Please try it with this change

     

    if
    [Set Up Date] >= List.Min(#"Date Table"[Start Date] and [Set Up Date] <= List.Max(#"Date Table"[End Date]) or
    [Senstive Field Change] >= List.Min(#"Date Table"[Start Date]) and [Senstive Field Change] <= List.Max(#"Date Table"[End Date])

    then "Current Month"

    else "Prior Period"

     

    If that is slow, you could also find the min and max dates in separate steps and then just reference those steps instead.

     

    Pat

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Afternoon,

       

      It is showing the below error now 

       

       

      I have created a list between my two table on the table Date Table [Date List] and formated it as numbers, i have also formatted the two Dates are numbers to try and align them, please see snip below. 

       

       

       

       

      So my current query is 

      if
      [Set Up Date] >= List.Min(#"Date Table"[DateList] and [Set Up Date] <= List.Max(#"Date Table"[DateList]) or
      [Senstive Field Change] >= List.Min(#"Date Table"[DateList]) and [Senstive Field Change] <= List.Max(#"Date Table"[DateList]))

      then "Current Month"

      else "Prior Period"

       

       

      Any advice appreciated.  Thanks

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    I now have it working which is great 😄, however it is exceptionally slow.  Any hints on how to make it faster/better?

    I created a DateList between the start and end date, which is now references. 

    let
    StartDate = List.Min(#"Date Table"[DateList]),
    EndDate = List.Max(#"Date Table"[DateList])
    in
    if
    [Set Up Date] >= StartDate and [Set Up Date] <= EndDate or
    [Senstive Field Change] >= StartDate and [Senstive Field Change] <= EndDate

    then "Current Month"

    else "Prior Period"