Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DataGirl80
Frequent Visitor

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
v-jayw-msft
Community Support
Community Support

Hi @DataGirl80 ,

 

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))

3.PNG

 

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.

@DataGirl80 - 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

HI @edhans , @v-jayw-msft  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"))

 

Data.png

This is the graph that I am producing

 

Graphs.png

 

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 🙂

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

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

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.

2020-06-12 15_58_05-20200609 - Dates between ranges - Power BI Desktop.png

My sample PBIX file is here.



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
Ashish_Mathur
Super User
Super User

Hi,

Share a dataset and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
edhans
Super User
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.2020-06-09 14_24_29-Untitled - Power BI Desktop.png

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.