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

[genaussie]

Hi All,

I've been having some real troubles with this one. The requirements are:
- trying to calculate the green below, which would be dynamic based on the selected Reporting Period (slicer) by the user. I assume this would need to be a measure and not a column, as my understanding is that columns cannot be dynamic based on slicer selection.

- the purpose is for 2x single-selection filters that use the Slicer (new) visual, so the user can click on the slicer and only see the projects that have increased (or decreased) vs last period. These rely upon the Risk Forecast Movement calculated column - one is filtered for 'Increase' and the other for 'Decrease'.

- the idea is to align the movement of the selected project/period with the previous project/period, so a project does not disappear from the table even if the movement has changed from previous month. Only the selected period and previous period are relevant, and the Reportable Flag should equal "Yes" for both periods.

Column	Column	Column	Column	Calculated Column	Calculated Measure	Slicer Selection by user
Project Number	Reporting Period	Reportable Flag	Risk Forecast	Risk Forecast Movement	Current Period	Reporting Period = 15/11/2024	Reporting Period = 15/12/2024
1	15/10/2024	Yes	10			Increase
2	15/10/2024	Yes	20			Decrease
3	15/10/2024	Yes	30
1	15/11/2024	Yes	15	Increase		Increase	Decrease
2	15/11/2024	Yes	5	Decrease		Decrease	Increase
3	15/11/2024	Yes	30				Increase
1	15/12/2024	Yes	10	Decrease	Current Period		Decrease
2	15/12/2024	Yes	25	Increase	Current Period		Increase
3	15/12/2024	Yes	40	Increase	Current Period		Increase

[DataNinja777 · Accepted solution]

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,

[genaussie · Accepted solution]

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])

[Kedar_Pande]

@genaussie 

Create a Measure for Risk Forecast Movement:

RiskForecastMovement = 
VAR CurrentPeriod = MAX('Table'[Reporting Period])
VAR PreviousPeriod = 
CALCULATE(
MAX('Table'[Reporting Period]),
FILTER(
ALL('Table'),
'Table'[Reporting Period] < CurrentPeriod
&& 'Table'[Project Number] = MAX('Table'[Project Number])
)
)
VAR CurrentForecast = 
CALCULATE(
MAX('Table'[Risk Forecast]),
FILTER('Table', 'Table'[Reporting Period] = CurrentPeriod)
)
VAR PreviousForecast = 
CALCULATE(
MAX('Table'[Risk Forecast]),
FILTER('Table', 'Table'[Reporting Period] = PreviousPeriod)
)
RETURN
IF(
NOT(ISBLANK(CurrentForecast)) && NOT(ISBLANK(PreviousForecast)),
IF(CurrentForecast > PreviousForecast, "Increase", "Decrease"),
BLANK()
)

Create a Measure to Check Reportable Flag

IsReportable = 
VAR CurrentPeriod = MAX('Table'[Reporting Period])
VAR PreviousPeriod = 
CALCULATE(
MAX('Table'[Reporting Period]),
FILTER(
ALL('Table'),
'Table'[Reporting Period] < CurrentPeriod
&& 'Table'[Project Number] = MAX('Table'[Project Number])
)
)
VAR CurrentReportable = 
CALCULATE(
MAX('Table'[Reportable Flag]),
FILTER('Table', 'Table'[Reporting Period] = CurrentPeriod)
)
VAR PreviousReportable = 
CALCULATE(
MAX('Table'[Reportable Flag]),
FILTER('Table', 'Table'[Reporting Period] = PreviousPeriod)
)
RETURN
IF(CurrentReportable = "Yes" && PreviousReportable = "Yes", 1, 0)

Create a measure to use in the slicer or visual for filtering projects based on movement.

FilteredMovement = 
IF(
[IsReportable] = 1,
[RiskForecastMovement],
BLANK()
)

💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[genaussie]

@DataNinja777 thank you so much, I have verified this solution works and is an excellent solution 😊

[DataNinja777 · Accepted solution]

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,

[genaussie]

@DataNinja777 to confirm, is there another step (prior to step 4) where you are required to build a Disconnected Table, as per this post? The table should only have the options Increase/Decrease. This worked for me.