Forum Discussion

edddddddd's avatar
edddddddd
Frequent Visitor
1 year ago
Solved

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...
  • rajendraongole1's avatar
    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.