To show change in sales from last month forecast vs current month forecast with past 5 yrs sales

[Baba]

Hello,

I am looking to get some help/guidance on how to get the difference between the sales (only for the current forecast vs last forecast) and Budget vs Current forecast when comparing sales with past 5 years.  I also attached me excel outcome what I am after.

So far I am able to get past 5 yrs sales along with current and last month forecast when selecting current year in the slicer but would like to see the difference too.

Outcome I am after:

I have my past sales data, current month sales data, last month sales data and my budget (Which I am referring as WP on the snip).  Then I have my calendar table for slicer, my disconneted data table for martix and the layout table for headers (as you can see to show "RF_2+10" for current and "RF_1+11" for past monht etc.

To calculate my mesure I am doing this and would like to know how I can bring "Differences" columns either using this measure or other...

Thanks in advance!

[Baba]

Any update anyone? - Thanks

[v-jingzhan-msft]

Hi @Baba 

Do you want to add the first 5 columns to the matrix visual? 

I see you have prepared a layout table for headers. If you want those columns, you also need to add them to the layout table. Then in the measure for the value, you can use switch function to provide different calculations based on different column values. For example, 

Value measure =
SWITCH (
    SELECTEDVALUE ( 'Layout'[Header] ),
    "RFC VAL", [Measure for RFC VAL],
    "LRFC VAL", [Measure for LRFC VAL],
    "B(W) VAL", [Measure for RFC VAL] - [Measure for LRFC VAL],
    [Last 5 Yr Sales]
)

Before creating above measure, you have to create measures for calculating those differences in advance. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[Baba]

Thanks @v-jingzhan-msft 

Yes, I want to add first 5 columns in the matrix visual.

Was able to add 3 out 5 (as you can see my first snip above) but struggling to add varinces columns.  One after CRF column to show the change between LRF and CRF (LRF - CRF = Change in RF) and one after WP column (CRF - WP = change between them).

I tried using your solution but it's not giving me the difference/change column.

This is the output after using above measure 

May be my "Layout" table is not properly setup?  I sorted the "Header" column by "Position" column.

[v-jingzhan-msft]

Hi @Baba 

The "Layout" table looks good. Can you try replacing the calculation [2024 RF_1+11] - [2024 RF_2+10] with a simple number like 1 to test whether the column can show or not? If it still wouldn't show, try providing the change column with another name without the triangle character. And check whether there is any leading or tailing invisible empty characters in the table.  

[Baba · Accepted solution]

Thanks @v-jingzhan-msft 
I tried all those things but not sure why it was not working till I used little different approach.
Instead of creating seprate measure for each, I used VAR and that resolved the issue.