Forum Discussion

ArchStanton's avatar
ArchStanton
Power Participant
1 year ago
Solved

Formatting Future Values in Matrix Visual Dynamically

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

  • Anonymous's avatar
    Anonymous
    1 year ago

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    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's avatar
      ArchStanton
      Power Participant

      Many thanks, this works perfectly! ğŸ˜Š