Power Query to Test if Date is between Start and End Dates including Nulls

This query looks at a Date (Today's Date) and checks if it is current, historical or upcoming relative to set start and end dates including nulls

me@jaykilleen.com wrote this about 7 years ago and it was last updated about 7 years ago.


← Back to the Posts

This post outlines to to output the following table:

StartDate EndDate Freshness
null 01/01/2018 Current
01/01/2016 null Current
01/01/2016 01/01/2017 Historical
01/01/2015 01/01/2020 Current
01/01/2020 null Upcoming
null null Current

It assumes that the CheckDate is equal to 22/03/2017 which was the date I created this query.

let
  // Variables for this query
  CheckDate = DateTime.Date(DateTime.LocalNow()), //Consider pulling this into a Parameter Query
  
  // Begin by creating some seed date to test our query against
  Source = #table(
    {"StartDate", "EndDate"}, 
    {
      {null,"01/01/2018"},
      {"01/01/2016",null},
      {"01/01/2016","01/01/2017"},
      {"01/01/2015","01/01/2020"},
      {"01/01/2020",null},
      {null,null}
    }
  ),
  // Change the date fields to date type
  ChangeType = Table.TransformColumnTypes(
    Source,
    {
      {"StartDate", type date}, 
      {"EndDate", type date}
    }
  ),
  // Add a column with the magic
  AddFreshness = Table.AddColumn(ChangeType, "Freshness", each 
    if        [EndDate] = null and 
              [StartDate] = null then "Current"
    else if   [StartDate] = null then 
      if      [EndDate] > CheckDate then "Current"
      else if [EndDate] < CheckDate then "Historical"
      else    "UnknownFreshness1"
    else if   [EndDate] = null then  
      if      [StartDate] < CheckDate then "Current"
      else if [StartDate] > CheckDate then "Upcoming"
      else    "UnknownFreshness2"
    else
      if      [StartDate] < CheckDate and 
              [EndDate] > CheckDate then "Current" 
      else if [StartDate] < CheckDate and [EndDate] < CheckDate then "Historical" 
      //else if [StartDate] > CheckDate and [EndDate] < CheckDate then "Impossible" 
      else if [StartDate] > CheckDate and [EndDate] > CheckDate then "Upcoming" 
      else "UnknownFreshness3"
  )
in
  AddFreshness

Similar