Forum Discussion
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
- Anonymous1 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 __formmatBest Regards,
Gao
Community Support TeamIf 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
Many thanks, this works perfectly! 😊
2 Replies
- AnonymousNot 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 __formmatBest Regards,
Gao
Community Support TeamIf 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
- ArchStantonPower Participant
Many thanks, this works perfectly! 😊