Forum Discussion
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
- Fowmy
Super User
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