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

 

Not sure what's your table looks like but you can get the start of last two and last four weeks and end of week by the measure as below.

start_of_last2_weeks = CALCULATE(MIN('Table'[Date]),WEEKNUM('Table'[Date],2)=WEEKNUM(TODAY(),2)-1)

start_of_last4_weeks = CALCULATE(MIN('Table'[Date]),WEEKNUM('Table'[Date],2)=WEEKNUM(TODAY(),2)-3)

end_of_week = CALCULATE(MAX('Table'[Date]),WEEKNUM('Table'[Date],2)=WEEKNUM(TODAY(),2))

 

Best Regards,

Jay

 

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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