cancel
Showing results for
Did you mean:
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
Community Support

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.

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

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:

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.

4 REPLIES 4
Community Support

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.

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

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:

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.

Frequent Visitor

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!

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

You should be able to use datesytd for till date number

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

Proud to be a Super User!

Frequent Visitor

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.

Announcements

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