Sales and Forecast in same matrix

[Anonymous]

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!

[v-rzhou-msft · Accepted solution]

Hi @Anonymous 

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. 

[Anonymous]

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]

@Anonymous , 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)

[Anonymous]

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.