Get Date on which Max or Min Value Occurred?

[zerosugar]

Hi all, I've been trying for some time to create a measure that gets, for each date of a time series, the date on which the maximum (or minimum) revenue, occurred. 

My data looks like this:

Cycle Scenario	Date	Predicted Quarter	Division	Revenue
Actuals	12/31/2020		Audit	74
Actuals	3/31/2021		Audit	85
Actuals	6/30/2021		Audit	66
Actuals	9/30/2021		Audit	76
Actuals	12/31/2021		Audit	84
Actuals	3/31/2022		Audit	67
Actuals	6/30/2022		Audit	94
Actuals	9/30/2022		Audit	54
Actuals	12/31/2020		Management Consulting	96
Actuals	3/31/2021		Management Consulting	70
Actuals	6/30/2021		Management Consulting	82
Actuals	9/30/2021		Management Consulting	94
Actuals	12/31/2021		Management Consulting	57
Actuals	3/31/2022		Management Consulting	85
Actuals	6/30/2022		Management Consulting	61
Actuals	9/30/2022		Management Consulting	99
Q1'22: Positive	3/31/2022	PQ0	Audit	67
Q1'22: Positive	6/30/2022	PQ1	Audit	79
Q1'22: Positive	9/30/2022	PQ2	Audit	67
Q1'22: Positive	12/31/2022	PQ3	Audit	62
Q1'22: Positive	3/31/2023	PQ4	Audit	86
Q1'22: Positive	3/31/2022	PQ0	Management Consulting	85
Q1'22: Positive	6/30/2022	PQ1	Management Consulting	66
Q1'22: Positive	9/30/2022	PQ2	Management Consulting	83
Q1'22: Positive	12/31/2022	PQ3	Management Consulting	69
Q1'22: Positive	3/31/2023	PQ4	Management Consulting	87
Q1'22: Negative	3/31/2022	PQ0	Audit	67
Q1'22: Negative	6/30/2022	PQ1	Audit	77
Q1'22: Negative	9/30/2022	PQ2	Audit	95
Q1'22: Negative	12/31/2022	PQ3	Audit	77
Q1'22: Negative	3/31/2023	PQ4	Audit	83
Q1'22: Negative	3/31/2022	PQ0	Management Consulting	85
Q1'22: Negative	6/30/2022	PQ1	Management Consulting	62
Q1'22: Negative	9/30/2022	PQ2	Management Consulting	55
Q1'22: Negative	12/31/2022	PQ3	Management Consulting	87
Q1'22: Negative	3/31/2023	PQ4	Management Consulting	98
Q2'22: Positive	6/30/2022	PQ0	Audit	94
Q2'22: Positive	9/30/2022	PQ1	Audit	84
Q2'22: Positive	12/31/2022	PQ2	Audit	65
Q2'22: Positive	3/31/2023	PQ3	Audit	70
Q2'22: Positive	6/30/2023	PQ4	Audit	94
Q2'22: Positive	6/30/2022	PQ0	Management Consulting	61
Q2'22: Positive	9/30/2022	PQ1	Management Consulting	78
Q2'22: Positive	12/31/2022	PQ2	Management Consulting	97
Q2'22: Positive	3/31/2023	PQ3	Management Consulting	98
Q2'22: Positive	6/30/2023	PQ4	Management Consulting	50
Q2'22: Negative	6/30/2022	PQ0	Audit	94
Q2'22: Negative	9/30/2022	PQ1	Audit	86
Q2'22: Negative	12/31/2022	PQ2	Audit	80
Q2'22: Negative	3/31/2023	PQ3	Audit	66
Q2'22: Negative	6/30/2023	PQ4	Audit	68
Q2'22: Negative	6/30/2022	PQ0	Management Consulting	61
Q2'22: Negative	9/30/2022	PQ1	Management Consulting	57
Q2'22: Negative	12/31/2022	PQ2	Management Consulting	55
Q2'22: Negative	3/31/2023	PQ3	Management Consulting	81
Q2'22: Negative	6/30/2023	PQ4	Management Consulting	94

This data of Actual Revenues as well as Revenue Forecasts. The Forecasts begin from an actual date (PQ0) and continue for 4 Quarters. The Forecasts have different Scenarios, like Positive, Negative, or Neutral. 

I would like to create a measure that can get, for a visual that groups by "Cycle Scenario" and "Revenue Date", a Column with the maximum date of revenue for that series. I would also like to exclude PQ0 from the calculation for Forecast Series. If there are two dates with the same value, take the first one.

Something like this essentially (dates may not be of max revenue, but hope this gets the point across):

Cycle Scenario	Revenue Date	Date of Max Revenue
Actuals	12/31/2020	12/31/2020
Actuals	3/31/2021	12/31/2020
Actuals	6/30/2021	12/31/2020
Actuals	9/30/2021	12/31/2020
Actuals	12/31/2021	12/31/2020
Actuals	3/31/2022	12/31/2020
Actuals	6/30/2022	12/31/2020
Actuals	9/30/2022	12/31/2020
Q1'22: Negative	3/31/2022	3/31/2023
Q1'22: Negative	6/30/2022	3/31/2023
Q1'22: Negative	9/30/2022	3/31/2023
Q1'22: Negative	12/31/2022	3/31/2023
Q1'22: Negative	3/31/2023	3/31/2023
Q1'22: Positive	3/31/2022	3/31/2023
Q1'22: Positive	6/30/2022	3/31/2023
Q1'22: Positive	9/30/2022	3/31/2023
Q1'22: Positive	12/31/2022	3/31/2023
Q1'22: Positive	3/31/2023	3/31/2023
Q2'22: Positive	6/30/2022	12/31/2022
Q2'22: Positive	9/30/2022	12/31/2022
Q2'22: Positive	12/31/2022	12/31/2022
Q2'22: Positive	3/31/2023	12/31/2022
Q2'22: Positive	6/30/2023	12/31/2022
Q2'22: Negative	6/30/2022	9/30/2022
Q2'22: Negative	9/30/2022	9/30/2022
Q2'22: Negative	12/31/2022	9/30/2022
Q2'22: Negative	3/31/2023	9/30/2022
Q2'22: Negative	6/30/2023	9/30/2022

I have tried a measure that gets:

VAR tempTable = GROUPBY('Table1', 'Table1'[Cycle Scenario], 'Table1'[Revenue Date], "MaxTotal", SUMX(CURRENTGROUP(), 'Table1'[Revenue]))

RETURN
MAXX(tempTable, [MaxTotal])

But this is not working. Also, it would only go as far as giving me the max series value for each series. But the GROUPBY() is giving me a table with what I want which is a view of the Series Revenue summarized by Cycle Scenario and Revenue Date. From there I just want to get the date of max or min revenue. Does anyone know how I could do this?

Thank you!

[v-rongtiep-msft]

Hi @zerosugar ,

Please refer to my pbix file.

Measure = var _1=MAXX(FILTER(ALL('Table'),'Table'[Cycle Scenario]=SELECTEDVALUE('Table'[Cycle Scenario])),'Table'[Revenue])
return
CALCULATE(MIN('Table'[Date]),FILTER(ALL('Table'),'Table'[Cycle Scenario]=SELECTEDVALUE('Table'[Cycle Scenario])&&'Table'[Revenue]=_1))

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

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

[zerosugar]

Thank you for the reply. This is close, but it is still not working correctly. This issue is that I don't want to see Business Divisions seperately, I want to see them aggregated. So, if I have a measure that uses ALL('1_MainData') in the MAXX, it will ignore that summing of Business Divisions.

I hope this image illustrates the issue:

My measure is V1, yours is V2. Note that for V2, it is not choosing the correct value for Actuals - This is because it is looking at the date for which Actuals had the single largest non-aggregate Revenue.

I made some changes to my code and saved it as Max Date V1:

Max Date V1 = 
VAR CycleScenarioTag = SELECTEDVALUE('1_MainData'[Cycle Scenario])
-- For Each date in each series, get the Sum of the Business Areas
VAR tempTable = GROUPBY('1_MainData', '1_MainData'[Cycle Scenario], '0_Calendar'[Date], 
    "MaxTotal", SUMX(CURRENTGROUP(), '1_MainData'[Revenue]))
-- Get the Max/Min of a Column in a temp table using MAXX or MINX
VAR myMax = MAXX(tempTable, [MaxTotal])
VAR myMin = MINX(tempTable, [MaxTotal])

RETURN
IF(
    -- If Negative scenario, find date on which Min occurred
    CONTAINSSTRING(SELECTEDVALUE('1_MainData'[Cycle Scenario]), "Negative"),
    MINX(
        FILTER(tempTable, [MaxTotal] = myMin),
        '0_Calendar'[Date]
    ),
    -- Oherwise, get date on which Max occurred
    MAXX(
        FILTER(tempTable, [MaxTotal] = myMax),
        '0_Calendar'[Date]
    )
)

Your code is measure "Max Date V2". I can see that my Max Date V1 works in the right chart, where there is no filtering coming from the [Revenue Date] column. However, when I add that filtering, my measure stops working. And I don't know how to remove that filtering because the date I need is coming from a Table Expression (tempTable in my measure).

In your V2, if you look at "Actuals", it selected 9/3/2022, even when that date had 153, whereas the true highest value is 170, on either 12/31/2020 or 9/30/2021. I am aggregating Audit and Management Consulting Divisions in that view. I think the V2 measure is not accounting for aggregations, as when I de-aggregate, it seems to work. However, my real dataset, I not only have more Divisions, but I also have Sub-Divisions, so this needs to work with an aggregated view.

Is there a way to remove the [Date] filter? It seems like that might fix the problem, but I'm not sure where something like that would even fit in? I think if I can remove the filter on [Revenue Date] in the GROUP('1_MainData') part of Max Date V1, it could work, but I'm not sure how to do that. ALLEXCEPT() doesn't seem to work and I don't know how to use CALCULATE() to implement REMOVEFILTERS().

Please let me know if this is sufficient information? Thank you!