Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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! 🙂
Solved! Go to 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.
add column: (Date) -> Age
Filter age greater than 120 days
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.
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.
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.
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 () ) )
Thanks a lot! Unfortunately, our security model doesn't allow users to add calculated columns. But thanks again for your suggestion!
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |