Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Check if date falls between two dates

Hi All   Hoping someone can help   I have a table which contains various data items for clients including a 'last contacted date'. I need to be able to identify whether the 'last contacted date'...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

HI edhans , Anonymous  and Ashish_Mathur 

 

Sorry for the late reply

 

Here is what my data looks like - I have the LatestContactDate is calcuated in the query editor using a conditional column to identify the latest date in either the DateofLatestVisit or LatestContact column. I have then, in the short-term used a fixed measure in the report to come up with the Contacted in Period:

 

Contacted In Period = if (Tbl_OpenCasesMerged[LatestContactDate]>=Date(2020,06,01) && Tbl_OpenCasesMerged[LatestContactDate]<=Date(2020,06,14),"Last Two Weeks",
if (Tbl_OpenCasesMerged[LatestContactDate]>=Date(2020,05,18) && Tbl_OpenCasesMerged[LatestContactDate]<=Date(2020,06,14),"Last Four Weeks","Not contacted"))

 

This is the graph that I am producing

 

 

What I would like to do is to be able to select a date using a slicer (which could inlcude a date in the future or in the past) and get it to automatically calculate whether the LatestContactDate was in the two weeks or four weeks prior to the selected date or not at all.

 

Hope that helps with the understanding of what I am trying to do.

 

Thanks 🙂

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Here is some sample data

       

      ID

      		DateofLatestVisitLatestContactLatestContactDateContacted In Period
      3485612/05/2020 12/05/2020Not contacted
      3819711/03/2020 11/03/2020Not contacted
      3819804/06/2020 04/06/2020Last Two Weeks
      3819904/06/2020 04/06/2020Last Two Weeks
      5706729/04/2020 29/04/2020Not contacted
      5918622/04/202004/06/202004/06/2020Last Two Weeks
      6194822/05/2020 22/05/2020Last Four Weeks
      6194927/03/2020 27/03/2020Not contacted
      6202711/05/202022/05/202022/05/2020Last Four Weeks
      7147721/05/2020 21/05/2020Last Four Weeks
      7390606/05/202022/05/202022/05/2020Last Four Weeks
      8896803/06/202003/06/202003/06/2020Last Two Weeks
      10160707/05/2020 07/05/2020Not contacted
      11390411/05/2020 11/05/2020Not contacted
      12186830/04/202002/06/202002/06/2020Last Two Weeks
      12757812/05/2020 12/05/2020Not contacted
      12758012/05/2020 12/05/2020Not contacted
      12905805/05/2020 05/05/2020Not contacted
      13348820/04/202029/05/202029/05/2020Last Four Weeks
      13495113/05/202005/06/202005/06/2020Last Two Weeks
      13558303/06/202022/05/202003/06/2020Last Two Weeks
      13737218/05/202018/05/202018/05/2020Last Four Weeks
      13777815/05/202028/05/202028/05/2020Last Four Weeks
      13788308/06/202003/06/202008/06/2020Last Two Weeks
      13885615/05/202005/06/202005/06/2020Last Two Weeks
      13885828/05/2020 28/05/2020Last Four Weeks
      13963308/06/202005/06/202008/06/2020Last Two Weeks
      14640227/05/2020 27/05/2020Last Four Weeks
      14745613/05/202001/06/202001/06/2020Last Two Weeks
      14925214/05/202005/06/202005/06/2020Last Two Weeks
      15023216/04/202029/05/202029/05/2020Last Four Weeks
      15445403/06/202022/05/202003/06/2020Last Two Weeks
      15514815/05/202029/05/202029/05/2020Last Four Weeks
      15516728/05/202028/05/202028/05/2020Last Four Weeks
      15605214/05/2020 14/05/2020Not contacted
      15626020/05/202004/06/202004/06/2020Last Two Weeks
      15694413/05/202005/06/202005/06/2020Last Two Weeks
      • edhans's avatar
        edhans
        Community Champion
        6 years ago

        This seems to work.

        Within Two Weeks = 
VAR VendorDate =
    MAX( 'Table'[LatestContact] ) 
VAR SelectedDates =
    ALLSELECTED( 'Date'[Date] )
VAR SelectedDate = [Selected Dates]
VAR DayCount = 14
VAR DateRange =
    DATESBETWEEN(
        'Date'[Date],
        SelectedDate - DayCount,
        SelectedDate
    )
VAR WithinDateRange = VendorDate
    IN DateRange
VAR Result =
    IF(
        HASONEVALUE( 'Date'[Date] ),
        WithinDateRange,
        "Multiple Selections"
    )
RETURN
    Result

        See below. You'll note the date I selected is June 1, so future dates are all showing false. You just need to replace the final logic of true/false with whatever you want to show.

        My sample PBIX file is here.