Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
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 - 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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHI @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:
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 🙂
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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHere 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 |
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi,
Share a dataset and show the expected result.
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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCheck out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
89 | |
83 | |
76 | |
64 |
User | Count |
---|---|
136 | |
111 | |
98 | |
97 | |
93 |