Formatting Future Values in Matrix Visual Dynamically

[ArchStanton]

Hi,

I've managed to successfully format future values (in RED) in my Matrix table below but not in a dynamic way, can anyone suggest a better way of doing this because I will need to manually change the conditional formatting criteria every month with the current method.

Current Method uses the 3315 Original Forecast value for Aug (last month) which I would need to physically change each month:

I'm not sure how to do this with measures.

The existing ones are:

Actual incl Projected = SUM(Modelling[Cumulative])

% Difference = 
([Actual incl Projected]-[Original Forecast]) / [Last Value Forecast]

Original Forecast = 
    VAR _firstdate = 
            CALCULATE(MIN('Total Closures'[Month]),
            FILTER('Modelling','Modelling'[Forecast] <> BLANK() )
            )
    RETURN
        CALCULATE(
                MIN('Modelling'[Forecast]),
                FILTER ('Modelling', Modelling[Month] = _firstdate)
        )

Last Value Forecast = 
    VAR _lastdate = 
            CALCULATE(MAX('Total Closures'[Month]),
            FILTER('Modelling','Modelling'[Forecast] <> BLANK() )
            )
    RETURN
        CALCULATE(
                MAX('Modelling'[Forecast]),
                FILTER ('Modelling', Modelling[Month] = _lastdate)
        )

Modelling Table:

                          Original Forcast

Can anyone help?

Thanks

[v-cgao-msft · Accepted solution]

Hi @ArchStanton ,

Please create a new measure and use it as the conditional format for both fields:

Measure = 
VAR __pre_month = EOMONTH(TODAY(),-2) + 1
VAR __cur_month = MIN(Modelling[Month])
VAR __pre_month_original_forecast = CALCULATE([Original Forecast],'Modelling'[Month]=__pre_month)
VAR __pre_month_diff = CALCULATE([% Difference],'Modelling'[Month]=__pre_month)
VAR __formmat = IF( __cur_month > __pre_month && [Actual incl Projected] > __pre_month_original_forecast && __pre_month_diff <= 1, "Red") 
RETURN
__formmat

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[v-cgao-msft · Accepted solution]

Hi @ArchStanton ,

Please create a new measure and use it as the conditional format for both fields:

Measure = 
VAR __pre_month = EOMONTH(TODAY(),-2) + 1
VAR __cur_month = MIN(Modelling[Month])
VAR __pre_month_original_forecast = CALCULATE([Original Forecast],'Modelling'[Month]=__pre_month)
VAR __pre_month_diff = CALCULATE([% Difference],'Modelling'[Month]=__pre_month)
VAR __formmat = IF( __cur_month > __pre_month && [Actual incl Projected] > __pre_month_original_forecast && __pre_month_diff <= 1, "Red") 
RETURN
__formmat

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[ArchStanton · Accepted solution]

Many thanks, this works perfectly! 😊