Dynamic Measure Calculation - Period over Period (vs last month)

Hi genaussie ,

To calculate the desired movement dynamically, we will use the following DAX measures:

1. Measure for Current Period Risk Forecast:

Current Period Risk Forecast = 
CALCULATE(
    MAX('Table'[Risk Forecast]),
    FILTER(
        'Table',
        'Table'[Reporting Period] = SELECTEDVALUE('Table'[Reporting Period]) &&
        'Table'[Reportable Flag] = "Yes"
    )
)

2. Measure for Previous Period Risk Forecast:

Previous Period Risk Forecast = 
CALCULATE(
    MAX('Table'[Risk Forecast]),
    FILTER(
        'Table',
        'Table'[Reporting Period] = EDATE(SELECTEDVALUE('Table'[Reporting Period]), -1) &&
        'Table'[Reportable Flag] = "Yes"
    )
)

3. Measure for Risk Forecast Movement:

Risk Forecast Movement Measure = 
VAR CurrentRisk = [Current Period Risk Forecast]
VAR PreviousRisk = [Previous Period Risk Forecast]
RETURN
    IF(
        ISBLANK(CurrentRisk) || ISBLANK(PreviousRisk),
        BLANK(),
        IF(
            CurrentRisk > PreviousRisk, 
            "Increase", 
            IF(
                CurrentRisk < PreviousRisk, 
                "Decrease", 
                "No Change"
            )
        )
    )

4. Measure for Filtering Projects Based on Slicer Selection:

Filtered Projects = 
VAR Movement = [Risk Forecast Movement Measure]
RETURN
    IF(
        Movement = SELECTEDVALUE('Slicer Table'[Movement]),
        1,
        0
    )

These measures dynamically calculate the movement based on the selected reporting period and ensure alignment between the current and previous periods. The Filtered Projects measure is applied as a visual-level filter set to = 1 in the table visual to filter projects based on the selected movement ("Increase" or "Decrease") in the slicer.

Best regards,

Hi All,
After some additional analysis, I have discovered the best and easiest solution for this particular scenario in my report is to:

1. Utilise the Risk Forecast Movement (Calculated Column) as the slicer AND

2. Any calculated measures in the Matrix table relating to Previous Period needs to ignore the above slicer by including in the calc: REMOVEFILTERS('Table'[Risk Forecast Movement])