Max date with conditions

[stewartad1]

Hi, 1st time poster here, hope this makes sense.

I need to retreive column "Current Risk Level" from table "RM_ImpactHistory" but with multiple conditions in place:
1. Find the max date but less than or equal to selectedDateTime, this is a variable coming from a slicer using selectedvalue

2. Risk ID being the same between these two tables: RM_ImpactAnalysisImpact and RM_ImpactHistory

Here is my current formula:

CurrRiskLevel =

MAXX(

    FILTER(RM_ImpactHistory,

    RM_ImpactHistory[RiskID] = RM_ImpactAnalysisImpact[RiskID] &&

    RM_ImpactHistory[RM_History.EventDate] <= [SelectedDateTime]

    ),

    RM_ImpactHistory[Current Risk Level]

)

Here's a picture of the report

Here's a picture of the corresponding data table

When 10/4/2024 4:23:33 PM is selected in the slicer, the expected result for "current risk level" should be "Medium: 12" and that is working. But when 11/6/2024 12:47:49 PM is selected in the slicer, the expected result for "current risk level" should be "Low: 8" and that is not working, it continues to show Medium: 12.

Here's a picture of the two options in the slicer

[v-zhengdxu-msft · Accepted solution]

Hi @stewartad1 

Please try this:

CurrRiskLevel =
VAR _MaxDate =
    MAXX (
        FILTER (
            ALLSELECTED ( RM_ImpactHistory ),
            RM_ImpactHistory[RiskID] = RM_ImpactAnalysisImpact[RiskID]
                && RM_ImpactHistory[RM_History.EventDate] <= [SelectedDateTime]
        ),
        RM_ImpactHistory[RM_History.EventDate]
    )
RETURN
    CALCULATE (
        SELECTEDVALUE ( RM_ImpactHistory[Current Risk Level] ),
        FILTER (
            ALLSELECTED ( RM_ImpactHistory ),
            RM_ImpactHistory[RM_History.EventDate] = _MaxDate
        )
    )

Don't use aggregate functions to process text-type data, the result of the MAX(RM_ImpactHistory[Current Risk Level]) is Medium: 12 but Low: 8 because the initial M is in front of L.

Hope it can help,

Best Regards

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

[stewartad1]

Hi, that worked, thank you so much!