Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Baba
Frequent Visitor

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

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.

Baba_0-1713219578155.png

Outcome I am after:

Baba_1-1713219719610.png

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.

 

Baba_2-1713220184305.png

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...

 

Baba_3-1713220296490.png

Baba_5-1713220480113.png

Thanks in advance!

1 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.

Baba_0-1713994528656.png

 

View solution in original post

5 REPLIES 5
Baba
Frequent Visitor

Any update anyone? - Thanks

v-jingzhan-msft
Community Support
Community Support

Hi @Baba 

 

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

vjingzhanmsft_0-1713342181352.png

 

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!

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.

 

Baba_0-1713382337820.png

This is the output after using above measure 

 

Baba_2-1713382866566.png

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

 

Baba_1-1713382791025.png

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.  

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.

Baba_0-1713994528656.png

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.