cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## 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' falls within a 2-week period of a selected date or a 4 week period.

This is for a return that needs to be made so the next time period will be:

Number of clients contacted between 01/06/2020 and 14/06/2020 (within the last 2-weeks) and number of clients contacted between 18/05/2020 and 14/06/2020 (within the last 4-weeks).

I also need to be able to show by exception which clients have not been contacted in either period so that teams can identify and prioritise who needs to be contacted before the 14/06/2020.

Following this return the period will then move on in two-week increments each time.

I have a calendar table in my model which I could use, but I can't work out the best measure or calculation to show the data in the way that I need to.

Thanks

8 REPLIES 8
Community Support

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.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Super User

@Anonymous - can you let us know if any of us are on the right track here? Thanks!

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

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 🙂

Super User

Can you provide sample source data. I'll repeat the links on how to do that. It is hard to work from screenshots as that requires a lot of typing on our part.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

Here is some sample data

 ID DateofLatestVisit LatestContact LatestContactDate Contacted In Period 34856 12/05/2020 12/05/2020 Not contacted 38197 11/03/2020 11/03/2020 Not contacted 38198 04/06/2020 04/06/2020 Last Two Weeks 38199 04/06/2020 04/06/2020 Last Two Weeks 57067 29/04/2020 29/04/2020 Not contacted 59186 22/04/2020 04/06/2020 04/06/2020 Last Two Weeks 61948 22/05/2020 22/05/2020 Last Four Weeks 61949 27/03/2020 27/03/2020 Not contacted 62027 11/05/2020 22/05/2020 22/05/2020 Last Four Weeks 71477 21/05/2020 21/05/2020 Last Four Weeks 73906 06/05/2020 22/05/2020 22/05/2020 Last Four Weeks 88968 03/06/2020 03/06/2020 03/06/2020 Last Two Weeks 101607 07/05/2020 07/05/2020 Not contacted 113904 11/05/2020 11/05/2020 Not contacted 121868 30/04/2020 02/06/2020 02/06/2020 Last Two Weeks 127578 12/05/2020 12/05/2020 Not contacted 127580 12/05/2020 12/05/2020 Not contacted 129058 05/05/2020 05/05/2020 Not contacted 133488 20/04/2020 29/05/2020 29/05/2020 Last Four Weeks 134951 13/05/2020 05/06/2020 05/06/2020 Last Two Weeks 135583 03/06/2020 22/05/2020 03/06/2020 Last Two Weeks 137372 18/05/2020 18/05/2020 18/05/2020 Last Four Weeks 137778 15/05/2020 28/05/2020 28/05/2020 Last Four Weeks 137883 08/06/2020 03/06/2020 08/06/2020 Last Two Weeks 138856 15/05/2020 05/06/2020 05/06/2020 Last Two Weeks 138858 28/05/2020 28/05/2020 Last Four Weeks 139633 08/06/2020 05/06/2020 08/06/2020 Last Two Weeks 146402 27/05/2020 27/05/2020 Last Four Weeks 147456 13/05/2020 01/06/2020 01/06/2020 Last Two Weeks 149252 14/05/2020 05/06/2020 05/06/2020 Last Two Weeks 150232 16/04/2020 29/05/2020 29/05/2020 Last Four Weeks 154454 03/06/2020 22/05/2020 03/06/2020 Last Two Weeks 155148 15/05/2020 29/05/2020 29/05/2020 Last Four Weeks 155167 28/05/2020 28/05/2020 28/05/2020 Last Four Weeks 156052 14/05/2020 14/05/2020 Not contacted 156260 20/05/2020 04/06/2020 04/06/2020 Last Two Weeks 156944 13/05/2020 05/06/2020 05/06/2020 Last Two Weeks
Super User

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.

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Super User

Hi,

Share a dataset and show the expected result.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

This will return true, false, or "multiple selections" if there isn't one date selected.

``````Within Two Weeks =
VAR VendorDate =
MAX( Vendors[Last Contacted Date] )
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
``````

The vendor table I mocked up is NOT filtered by the date table (not connected in the model view) because selecting a date removed everything that isn't that exact date. You can modify that to use 28 dates in the DayCount variable.

If you need more help, please provide sample data and more specifics.
How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.