DAX - need to return a value from a calculated column from between 2 dates

[Anonymous]

Please can somebody help with this, been struggling for days and I cant get my head around it. Our company runs a report 2 months behind. So when it's April, they're looking at February's data. 

I have some data that looks like the picture below. The first 3 columns are straight out of the data source.

The fourth column ('Most Recent Audit') is a calculated column I created to give me the most recent audit date per location (regardless of reporting period).

The DAX looks like this: 

Most Recent Audit = CALCULATE(LASTDATE(Table[Date Of Last Audit]),ALLEXCEPT(Table, Table[Location]))

I now need to create a new column that returns the value in the [Most Recent Audit] column, but only where

the Reporting Period is less than or equal to 28th Feb 2021 (The last day of the month which was 2 months ago)  AND is more than 28th Feb 2019 (the last day of the month which was 2 years & 2 months ago).

I have already created measures for these 2 dates:

Reporting Month = EOMONTH([Today],-2)

Reporting Month minus 2 years = EOMONTH([Today],-26)

So essentially I want to return the value from the [Most Recent Audit] column, but only where it falls between the Reporting Month and Reporting Month minus 2 years dates.

I'm stuck! Any ideas?

Perhaps the Reporting Month and Reporting Month Minus 2 years should be columns instead of measures??

[amitchandak · Accepted solution]

@Anonymous , Based on what I got. Try new measure

Most Recent Audit =
Var _max = EOMONTH([Today],-2)
Var _min = EOMONTH([Today],-26)
return
CALCULATE(max(Table[Date Of Last Audit]),filter(allselected(Table), Table[Location] = max(Table[Location]) && Table[Date Of Last Audit] >=_min && Table[Date Of Last Audit] <=_max ))

New column

Most Recent Audit =
Var _max = EOMONTH([Today],-2)
Var _min = EOMONTH([Today],-26)
return
CALCULATE(max(Table[Date Of Last Audit]),filter((Table), Table[Location] = earlier(Table[Location]) && Table[Date Of Last Audit] >=_min && Table[Date Of Last Audit] <=_max ))