Forum Discussion
Getting a DATEDIFF value from a date column and a slicer value
Hi all, and thanks in advance for any help!
I have a table with customer number (custNo) and date of form sent (createdOn; DD/MM/YYYY).
| custNo | createdOn |
| 111 | 01/01/2025 |
| 222 | 01/02/2025 |
| 333 | 01/03/2025 |
I need to create an aging table that references a date slicer (date slicer uses a calendar table); question to be answered is for the past 3/7/14 days (from the date selected from the slicer), how many customers sent a form? For example, if the user chooses 02/01/2025, the resultant table should count 1 (for customer "111"), if user chooses 02/02/2025 should show 2 etc.
I'd like to learn how to get the DATEDIFF if possible; just doing DATEDIFF(table[createdOn], SELECTEDVALUE(calendarTable[Date]), DAY) doesn't work, and I think I know why, but what methods can we use to get the DATEDIFF value, since I want a flexible solution as I'd probably have to use this DATEDIFF value again in future.
Any help appreciated. Thanks again!
Hi edddddddd - You can write a measure that calculates the number of customers who sent a form in the past 3, 7, or 14 days relative to the selected date in the slicer.
Eg.
Forms Sent in Past Days =
VAR SelectedDate = SELECTEDVALUE(calendarTable[Date]) -- The date selected in the slicer
VAR DaysToCheck = 3 -- Change this value to 7 or 14 for other periods
RETURN
COUNTROWS(
FILTER(
YourTable,
DATEDIFF(YourTable[createdOn], SelectedDate, DAY) <= DaysToCheck &&
DATEDIFF(YourTable[createdOn], SelectedDate, DAY) >= 0
)
)create
Create a disconnected table (e.g., periodtable) with values: (3,7,14)
PeriodTable = DATATABLE(
"Days", INTEGER,
{
{3},
{7},
{14}
}
)Now add slicer to the report (periodtable)
Update the measure to reference the selected period,
Forms Sent in Past Days (Dynamic) =
VAR SelectedDate = SELECTEDVALUE(calendarTable[Date]) -- The date selected in the slicer
VAR DaysToCheck = SELECTEDVALUE(PeriodTable[Days], 3) -- Default to 3 days if no selection
RETURN
COUNTROWS(
FILTER(
YourTable,
DATEDIFF(YourTable[createdOn], SelectedDate, DAY) <= DaysToCheck &&
DATEDIFF(YourTable[createdOn], SelectedDate, DAY) >= 0
)
)check this and i hope it works.
3 Replies
- rajendraongole1
Super User
Hi edddddddd - You can write a measure that calculates the number of customers who sent a form in the past 3, 7, or 14 days relative to the selected date in the slicer.
Eg.
Forms Sent in Past Days =
VAR SelectedDate = SELECTEDVALUE(calendarTable[Date]) -- The date selected in the slicer
VAR DaysToCheck = 3 -- Change this value to 7 or 14 for other periods
RETURN
COUNTROWS(
FILTER(
YourTable,
DATEDIFF(YourTable[createdOn], SelectedDate, DAY) <= DaysToCheck &&
DATEDIFF(YourTable[createdOn], SelectedDate, DAY) >= 0
)
)create
Create a disconnected table (e.g., periodtable) with values: (3,7,14)
PeriodTable = DATATABLE(
"Days", INTEGER,
{
{3},
{7},
{14}
}
)Now add slicer to the report (periodtable)
Update the measure to reference the selected period,
Forms Sent in Past Days (Dynamic) =
VAR SelectedDate = SELECTEDVALUE(calendarTable[Date]) -- The date selected in the slicer
VAR DaysToCheck = SELECTEDVALUE(PeriodTable[Days], 3) -- Default to 3 days if no selection
RETURN
COUNTROWS(
FILTER(
YourTable,
DATEDIFF(YourTable[createdOn], SelectedDate, DAY) <= DaysToCheck &&
DATEDIFF(YourTable[createdOn], SelectedDate, DAY) >= 0
)
)check this and i hope it works.
Thanks so much! I'll give it a try and report back.
Hi edddddddd ,
After testing, I think rajendraongole1's answer is great. Can rajendraongole1's answer solve your problem?
If solved, don't forget to accept rajendraongole1's reply as a solution to help more others facing the same problem to find a solution quickly, thank you very much!
Best Regards,
Dino Tao
