Getting a DATEDIFF value from a date column and a slicer value

[edddddddd]

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!

[Anonymous]

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

[rajendraongole1 · Accepted solution]

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.

[edddddddd]

Thanks so much! I'll give it a try and report back.