Forum Discussion
Getting a DATEDIFF value from a date column and a slicer value
- 1 year ago
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.
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.