Relative date "not in last 3 months"

[KMZ_ESS]

Dear all,

I want to measure how many accounts of a certain category have not been visited in the last 3 months (using the field "last visit date"). The relative date filter allows to show the last 3 months, but I can't make it show entries with a date NOT in the last 3 months. If I use the advanced filter I can use the NOT operator, however the date range would be static and would not show the last 3 months with relation to the date of the query but a fixed 3 month range.

Is there a way to create the relative date filter with a NOT operator in a measure? Thanks a lot! 🙂

[joxieblue]

add column: (Date) -> Age

Filter age greater than 120 days

[v-yangliu-msft · Accepted solution]

Hi  @KMZ_ESS ,

You can try the following measure:

Measure =
var _last3=DATE(YEAR(TODAY()),MONTH(TODAY())-3,DAY(TODAY()))
return
COUNTX(
    FILTER(ALL('Table'),
    'Table'[Date]>=_last3&&'Table'[Date]<=TODAY()&&'Table'[category]="A"),[Date])

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[johnt75]

The built-in functionality of the relative date filter is really lacking, there are a ton of improvement which could be made.

To get around it for your use case you could add a calculated column to your date table like

In Last 3 Months =
[Date] >= ( EOMONTH ( TODAY (), -4 ) + 1 )
&& [Date] <= TODAY ()

and then use that column as a filter.

[SDittmannFleet]

This column flags items that are actually farther away than 3 months. Today is 6/14/2023, and per Google, 3 months before today was 3/14/2023. However, it returned "true" for a date of 3/3/2023.

[johnt75]

Yes, this solution works in whole months. If you wanted partial months you could try something like

Date in last 3 months =
[Date] <= TODAY ()
    && [Date]
        >= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 3, DAY ( TODAY () ) )

[KMZ_ESS]

Thanks a lot! Unfortunately, our security model doesn't allow users to add calculated columns. But thanks again for your suggestion!