calculate not respecting its own filter

[AEIOUandY]

Hoping I can get some help here. I have a measure using another measure as a filter value to try to find the earliest date for which a column equals the filter value. I'm getting strange behaviour from the peakdate measure, however.

I have a table: TABLE, with columns DATECOL and INDICATOR. The date range in this table is not continuous, but spans more than a year,and ends on the current day (and there are data for that current day). 

I have a measure, PEAK, that finds the maximum value of INDICATOR that falls within a date range.

PEAK = calculate(maxx('TABLE', 'TABLE'[INDICATOR]),'TABLE'[DATECOL]>=date(2021,12,01)&&'TABLE'[DATECOL]<=date(2022,03,01))
This measure seems to work fine - finds the peak value in that date range, and I can confirm that by visually inspecting values in the table.

I have ANOTHER measure, DATEOFPEAK, that I want to return the earliest date in DATECOL which the PEAK is observed (in this case, it occurs on multiple days). 

DATEOFPEAK = calculate(min('TABLE'[DATECOL]),filter(all('TABLE'),'TABLE'[INDICATOR]=[PEAK]&&'TABLE'[DATECOL]>=date(2021,12,01)&&'TABLE'[DATECOL]<=date(2022,03,01))

This will not work, it returns the minimum date specified by the filter (2021,12,01) rather than the minimum date at which the PEAK value is observed in the INDICATOR column. I have ensured that the variables are formatted in the same way. In the original table (which is a calculated table, although I'm having the same problem whether my PEAK measure is based on a preexisting column, a calculated column, or a column in a calculated table), INDICATOR is rounded to 0 (in the actual column calculation in the summarize DAX command). 

I've tried swapping firstdate for min and I've tried restructuring as a MINX, and everything just returns the minimum date value specified in the PEAK measure but it's ignoring the part of the filter that directs it to filter INDICATOR down to only values that match PEAK.

Please, what am I missing?

[johnt75 · Accepted solution]

You could try

DATEOFPEAK =
SELECTCOLUMNS (
    TOPN (
        1,
        FILTER (
            ALL ( 'TABLE' ),
            'TABLE'[DATECOL] >= DATE ( 2021, 12, 1 )
                && 'TABLE'[DATECOL] <= DATE ( 2022, 3, 1 )
        ),
        [PEAK], DESC,
        'TABLE'[DATECOL], ASC
    ),
    "@val", 'TABLE'[DATECOL]
)

[AEIOUandY]

A colleague ended up resolving this by putting a variable definition for the PEAK in the DATEOFPEAK calculation rather than referring to it as a measure, and that one change allowed PBI to properly return the DATEOFPEAK value. 

Thus, OUR solution was:
PEAKDATE = 
var PEAK = calculate(maxx('TABLE','TABLE'[INDICATOR],datesinperiod('TABLE'[DATECOL],date(2022,03,01,-91,day))

return calculate(min('TABLE'[DATECOL]),filter['TABLE','TABLE'[INDICATOR]=PEAK&&'TABLE'[DATECOL]>=date(2021)&&tab,12,01)&&'TABLE'[DATECOL]<=date(2022,03,01)))

The calculation you provided works smoothly and does not require me to prepopulate a variable, however, so I think it's cleaner. Thank you so much for providing this option - I'll be checking for calculation speed and probably changing my definitions to match your solution.