Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Dynamic date range slicer with only the max date (end date) selected and ignoring the min date.


How can I adjust my Power BI measure to calculate the total number of inactive clients beyond two years, based on the maximum date selected in a date range slicer, while ignoring the minimum date? For example, if the date range selected is from 1/1/2021 to 1/1/2022, the measure will show blank values since the selected range is less than 2 years. Instead, I want it to consider the maximum date selected (1/1/2022) and calculate the total from that date back to two years (1/1/2020) even if it's not included in the slicer

Here is the measure
InactiveClients_Beyond2Years =
CALCULATE(
COUNTROWS(
FILTER(
VALUES(SQL_Database_Website[NTID]),
CALCULATE(MAX(SQL_Database_Website[SubmitDate])) <= MAX(SQL_Database_Website[SubmitDate]) - 730
)
),
ALLSELECTED(SQL_Database_Website[SubmitDate])

 

  • Anonymous 
    Please try this meaure:

    InactiveClients_Beyond2Years =
    VAR __CurrentDate =
        MAX ( SQL_Database_Website[SubmitDate] )
    VAR __PrevDate =
        EDATE ( __CurrentDate, -24 )
    VAR __Result =
        CALCULATE (
            DISTINCTCOUNT ( SQL_Database_Website[NTID] ),
            SQL_Database_Website[SubmitDate] >= __PrevDate,
            SQL_Database_Website[SubmitDate] <= __CurrentDate
        )
    RETURN
        __Result

1 Reply

  • Anonymous 
    Please try this meaure:

    InactiveClients_Beyond2Years =
    VAR __CurrentDate =
        MAX ( SQL_Database_Website[SubmitDate] )
    VAR __PrevDate =
        EDATE ( __CurrentDate, -24 )
    VAR __Result =
        CALCULATE (
            DISTINCTCOUNT ( SQL_Database_Website[NTID] ),
            SQL_Database_Website[SubmitDate] >= __PrevDate,
            SQL_Database_Website[SubmitDate] <= __CurrentDate
        )
    RETURN
        __Result