Forum Discussion
Format future values in Area Chart
Hi,
I have the following visuals that include predicted figures for September onwards:
Is there any way I can:
- Shade the area from Sep onwards in the Area Chart Visual to show these are predicted values?
The current code that sit behind these visuals are:
Cumulative = SUM(Modelling[Cumulative])
Original Forecast =
VAR _firstdate =
CALCULATE(MIN('Total Closures'[Month]),
FILTER('Modelling','Modelling'[Forecast] <> BLANK() )
)
RETURN
CALCULATE(
MIN('Modelling'[Forecast]),
FILTER ('Modelling', Modelling[Month] = _firstdate)
)
% Difference =
(Modelling[Last Value Adjusted] - [Last Value Forecast2]) / [Last Value Forecast2]
Thanks
Hi,ArchStanton .Thank you for your reply.
I somewhat didn't understand what you need behind it , but I'm guessing that's the case, hope it helps.
create measures:M_Cumulative02 = CALCULATE(SUM('Modelling'[Cumulative]),'Modelling'[PastorFuture]="feature")ISFuture = IF(MAX('Modelling'[PastorFuture])="feature",1,0)Format your visual >Visual >Data labels>Value>Color>conditional formatting
If you want to set the color of the shadows under the line graph uniformly (set the color of all predicted value ranges uniformly to the same color), unfortunately I haven't found a suitable way to do that at the moment.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
9 Replies
Hey,
There is no direct way of doing it, however you can split your measures into 2.
I.e 1 measure will plot April - September, 2nd measure will plot April - March.
You can then overlay the 2 measures on your chart effectively hiding 1 behind the other and thus enabling you to change the colours of both seperately.
Thanks for your advice. I'm not quite sure how to do this.
My data table is below and is linked to excel. The month is many to many relationship with my Calendar table:It is the Cumulative Column that i would need to base the 2 measures for (Apr - Aug) & (Sep to Mar).
The current measure for that column is just a basic Sum =
Actual incl Projected = SUM(Modelling[Cumulative])My Calendar Table has a Month Year Sort columns that would show 202401 (Apr) and 202405 (Aug), 202406 (Sep) to 202512 (Mar).
Apr to Aug = CALCULATE( FILTER('Date','Date'[Month Year Sort])
- You can use your measure 'Actual incl Projected' with no change, you will need to create a new measure which will restrict your date range.Cut Off =CALCULATE([Running Forecast] (Replace with [Actual incl Projected]),ALL('Calendar'[Week Number]),'Calendar'[Rolling Financial Year] = 0 && 'Calendar'[Week Number] <= 29)
Here you will need to change the red highlighted area to filter to your criteria. I do not know what attributes you have avaliable to you however i would suggest using something that will make this dynamic.
Thanks.
I've modified your code to this but nothing changes on the visual:Cut Off = CALCULATE( [Actual incl Projected], ALL('Date'[Calendar Week]), 'Date'[Yr & Mth Number] = 202408 && 'Date'[Calendar Week] < 24 )I tried to change the Cut Off Marker colour from dark green to something brighter but it does not appear in the series drop-down options.
Something tells me i'm not a million miles away from solving this with your help.The Red values have been conditionally formatted on a calculated column I created that provides Past or Future values.
I tried using that in place of your 'Calendar'[Rolling Financial Year] = 0here but I get an error message:
Cut Off =Cut Off = CALCULATE( [Actual incl Projected], ALL('Date'[Calendar Week]), 'Modelling'[PastorFuture] = "Past" && 'Date'[Calendar Week] < 24
Hello,ajohnso2 ,thanks for your concern about this issue.
Your answer is excellent!
And I would like to share some additional solutions below.
Hi,ArchStanton .I am glad to help you.
As ajohnso2 suggested.
You only need to write the measure you want to display the expected value as two separate measures
like this.
Create a column of calculations (or measures) to be used as markersOriginal Forecast02 = VAR _firstdate = CALCULATE(MIN('Total Closures'[Month]), FILTER('Modelling','Modelling'[Forecast] <> BLANK() ) ) RETURN CALCULATE( MIN('Modelling'[Forecast]), FILTER ('Modelling', Modelling[Month] = _firstdate &&'Modelling'[PastorFuture]="feature") )You need to pay attention to the order in which the fields are displayed in Y-axis; the fields below will override the fields above them.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Thank you so much! this now works exactly as in your pbix file - much appreciated.
There is one more thing, is it possible to shade the area higlighted with the arrow? The projected figures are on top so it would make sense to shade these and not the ones you have done?
Hi,ArchStanton .Thank you for your reply.
I somewhat didn't understand what you need behind it , but I'm guessing that's the case, hope it helps.
create measures:M_Cumulative02 = CALCULATE(SUM('Modelling'[Cumulative]),'Modelling'[PastorFuture]="feature")ISFuture = IF(MAX('Modelling'[PastorFuture])="feature",1,0)Format your visual >Visual >Data labels>Value>Color>conditional formatting
If you want to set the color of the shadows under the line graph uniformly (set the color of all predicted value ranges uniformly to the same color), unfortunately I haven't found a suitable way to do that at the moment.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
