Applying Date and category filters & extending the filter date range past the filtered report range

[hanzfunny]

The report is filtered by 1 month, but 1 column needs to look at the last 3 months' activity data.

The max activity date needs to be less than the max report period date and also filter for a specific activity type. 

Table = 'dl FactActivityBreakdown'

Table Filtering = [ActivityStartDate]

Related filter = 'dl DimActivityClassification'[ActivityClassificationCode (groups)])="Collaborative Clinical Review"

This is what I have tried:

Error Message – A FUNCTION FILTER HAS BEEN USED IN A TRUE/FALSE EXPRESSION THAT IS USED AS A TABLE FILTER.

Measure 1= 

var _enddatecontext=MAX('transform DimCalendar'[ActualDate])

Var _Startdatecontext=MAX('transform DimCalendar'[ActualDate])-92

Return

CALCULATE(

    MAX('dl FactActivityBreakdown'[ActivityStartDate]),

    FILTER('dl FactActivityBreakdown',RELATED('dl DimActivityClassification'[ActivityClassificationCode (groups)])="Collaborative Clinical Review")&&'dl FactActivityBreakdown'[ActivityStartDate]<=_enddatecontext)

Error Message – A FUNCTION FILTER HAS BEEN USED IN A TRUE/FALSE EXPRESSION THAT IS USED AS A TABLE FILTER.

Measure 2=

var _enddatecontext=MAX('transform DimCalendar'[ActualDate])

Var _Startdatecontext=MAX('transform DimCalendar'[ActualDate])-92

Return

CALCULATE(

    MAX('dl FactActivityBreakdown'[ActivityStartDate]),

    FILTER('dl FactActivityBreakdown','dl FactActivityBreakdown'[ActivityStartDate]>=_Startdatecontext)&&'dl FactActivityBreakdown'[ActivityStartDate]<=_enddatecontext,

    CALCULATE(  

    FILTER('dl FactActivityBreakdown',RELATED('dl DimActivityClassification'[ActivityClassificationCode (groups)])="Collaborative Clinical Review"))

)

This one has the max date and filters the activities correctly, but doesn’t have the date filter applied or use the variables. 

Measure 3 = 

var _enddatecontext=MAX('transform DimCalendar'[ActualDate])

Var _Startdatecontext=MAX('transform DimCalendar'[ActualDate])-92

Return

CALCULATE(

    MAX('dl FactActivityBreakdown'[ActivityStartDate]),

    FILTER('dl FactActivityBreakdown',RELATED('dl DimActivityClassification'[ActivityClassificationCode (groups)])="Collaborative Clinical Review"))

[Anonymous]

Hi @hanzfunny ,

For Date filter, please simply try:

Measure = 
var _enddatecontext=MAX('Calendar'[Date])
var _Startdatecontext=MAX('Calendar'[Date])-92
Return
CALCULATE(MAX('Table'[Date]),FILTER('Table',[Date]>=_Startdatecontext && [Date]<=_enddatecontext))

And It seems that there is another filter——DimActivityClassification'[ActivityClassificationCode (groups)]="Collaborative Clinical Review",right?

You may provide some dummy data table and define the relationships to help us clarify your scenario.Or share me with your pbix file after removing sensitive data.

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

[hanzfunny]

Here's the new version of the measure so far. (It doesn't work yet). Do I need to use the ALL function somewhere in the measure to override the page filter? 

Max Date Collaborative Clinical Review =

var _enddatecontext=MAX('transform DimCalendar'[ActualDate])

Var _Startdatecontext=MAX('transform DimCalendar'[ActualDate])-92

var _maxdate=CALCULATE(MAX('dl FactActivityBreakdown'[ActivityStartDate]),FILTER('dl FactActivityBreakdown','dl FactActivityBreakdown'[ActivityStartDate]>=_Startdatecontext && 'dl FactActivityBreakdown'[ActivityStartDate]<=_enddatecontext))

var _ClinicalReview=FILTER('dl FactActivityBreakdown',RELATED('dl DimActivityClassification'[ActivityClassificationCode (groups)])="Collaborative Clinical Review")

Var _RESULT=CALCULATE(MAX('dl FactActivityBreakdown'[ActivityStartDate])=(_ClinicalReview)&&_maxdate)

RETURN

_RESULT

[hanzfunny]

I tried the date range with the -92 but it doesn't seem to get around the page filter of the reporting month. 
I think it's how it's linked with the actual date column. This is filtering out the other activities. The page filter uses the actual date to create a reporting month based on the 1st and last day of the month. 

The other filter is this one. The classification types have been grouped on the data page. 

 MAX('dl FactActivityBreakdown'[ActivityStartDate]),

    FILTER('dl FactActivityBreakdown',RELATED('dl DimActivityClassification'[ActivityClassificationCode (groups)])="Collaborative Clinical Review"))

[lbendlin]

Do not use RELATED in a measure. Let the data model do the work.