cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sophhhlee
Frequent Visitor

Sales and Forecast in same matrix

Hi I am a total beginner to PBI and have only just started picking it up these few days. I have been tasked to do up a Demand Waterfall Matrix on Powerbi, to analyse accuracy in forecasts and sales over time. 

 

I have 2 main datasets, one consists of past sales data and the other consists of future monthly forecast data. I have already populated a matrix visualisation (Month by Month) with monthly forecasts, where the Row months show when the forecast was entered, and the Column months show when the forecast is expected. However, for months that have already passed, the cells should be filled with Sales figures of that month. 

 

1. Can i ask if it is possible to add in past sales data on the same matrix, while keeping only 1 row and 1 column for each month? Do I need to append the sales data to the forecast data table? 

2. How do I duplicate each rows in my Sales data in Power Query so that the sales figure appears for every month up till the current month? For example, for the sales that occured in 2020 Jan, how do I populate the rest of the months (2020 Jan-2021 June) with this same figure? Currently, it would only show up in 2020 Jan-2020 Jan in my matrix, but I want it to show for the rest of the following months too. 

 

Thank you and I would really appreciate any help!

1 ACCEPTED SOLUTION
RicoZhou
Community Support
Community Support

Hi @sophhhlee 

You can build a measure to show Sales values from Jan to Jun and show Foreast values in month after June in matrix.

My Sample, Foreast table is from 2021/01/01 to 2021/12/31.

1.png

Sales table is from 2021/01/01 to Today(2021/06/15)

2.png

So the result we want is to show sales between Jan to Jun and show foreast between Jul to Dec in Matrix.

I build a date table by dax code.

Calendar = ADDCOLUMNS(CALENDARAUTO(),"Year",YEAR([Date]),"Month",MONTH([Date]),"YearMonth",YEAR([Date])*100+MONTH([Date]))

Measures:

Base Value = 
VAR _MinDate =
    MIN ( Sales[Sales] )
VAR _MaxDate =
    MAX ( Sales[Date] )
VAR _MinYearMonth =
    YEAR ( _MinDate ) * 100
        + MONTH ( _MinDate )
VAR _MaxYearMonth =
    YEAR ( _MaxDate ) * 100
        + MONTH ( _MaxDate )
VAR _Sales =
    CALCULATE (
        SUM ( Sales[Sales] ),
        FILTER ( Sales, Sales[Category] = MAX ( Foreast[Category] ) )
    )
VAR _Foreast =
    SUM ( Foreast[Foreast] )
RETURN
    IF (
        AND (
            MAX ( 'Calendar'[YearMonth] ) >= _MinYearMonth,
            MAX ( 'Calendar'[YearMonth] ) <= _MaxYearMonth
        ),
        _Sales,
        _Foreast
    )
Value = SUMX(Foreast,[Base Value])

Matrix:

3.png

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

 

View solution in original post

4 REPLIES 4
RicoZhou
Community Support
Community Support

Hi @sophhhlee 

You can build a measure to show Sales values from Jan to Jun and show Foreast values in month after June in matrix.

My Sample, Foreast table is from 2021/01/01 to 2021/12/31.

1.png

Sales table is from 2021/01/01 to Today(2021/06/15)

2.png

So the result we want is to show sales between Jan to Jun and show foreast between Jul to Dec in Matrix.

I build a date table by dax code.

Calendar = ADDCOLUMNS(CALENDARAUTO(),"Year",YEAR([Date]),"Month",MONTH([Date]),"YearMonth",YEAR([Date])*100+MONTH([Date]))

Measures:

Base Value = 
VAR _MinDate =
    MIN ( Sales[Sales] )
VAR _MaxDate =
    MAX ( Sales[Date] )
VAR _MinYearMonth =
    YEAR ( _MinDate ) * 100
        + MONTH ( _MinDate )
VAR _MaxYearMonth =
    YEAR ( _MaxDate ) * 100
        + MONTH ( _MaxDate )
VAR _Sales =
    CALCULATE (
        SUM ( Sales[Sales] ),
        FILTER ( Sales, Sales[Category] = MAX ( Foreast[Category] ) )
    )
VAR _Foreast =
    SUM ( Foreast[Foreast] )
RETURN
    IF (
        AND (
            MAX ( 'Calendar'[YearMonth] ) >= _MinYearMonth,
            MAX ( 'Calendar'[YearMonth] ) <= _MaxYearMonth
        ),
        _Sales,
        _Foreast
    )
Value = SUMX(Foreast,[Base Value])

Matrix:

3.png

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

 

View solution in original post

Thank you so much for the detailed response, Rico! I have resolved this issue on my own, but i am sure this would help others who are facing a similar problem. Really appreciate it! 

amitchandak
Super User IV
Super User IV

@sophhhlee , You have to create a common date table. If you do not have date create with help from month year.

I have explained that at start of this video

https://www.youtube.com/watch?v=yPQ9UV37LOU

 

You should be able to use datesytd for till date number

 

Year Rank = RANKX(all('Date'),'Date'[Year Start date],,ASC,Dense)



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Thank you for your response @amitchandak 

 

So after I create the common date table, do I use the dates in this common table as 'Rows' and 'Columns' in the report, and the Sales data and forecast data as 'Values'? I am struggling as the forecast numbers for each month are not appearing according to the specified 'Row' months. 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors