Forum Discussion

meetrachhadia9's avatar
meetrachhadia9
Regular Visitor
6 years ago
Solved

How to add zero values for missing sales months so the data is evenly spaced for forecasting?

I am trying to do forecasting in Power BI for a very large number of parts. For parts having demand in every month, the forecasting runs perfectly. For parts which does not have demand in some months...
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi meetrachhadia9,

    Did your table contain all month and parts? If this is a case, you can create new tables to extract and store 'parts' and 'month' values.
    Then you can create matrix visual with new tables fields as axis and write a measure to lookup value from the original table based on current part and month. (matrix design: month to column, part to row, measure to value)

    Calculate tables: 

     

    MonthAxis = VALUES('Sample'[Month])
    
    PartAxis = VALUES('Sample'[Part])

     

    Measure:

     

    Measure =
    VAR currMonth =
        VALUES ( MonthAxis[Month] )
    VAR currPart =
        VALUES ( PartAxis[Part] )
    RETURN
        CALCULATE (
            SUM ( 'Sample'[Amount] ),
            FILTER ( ALLSELECTED ( 'Sample' ), [Month] IN currMonth && [Part] IN currPart )
        ) + 0
    

     

    If your source table only contains less amount of records that can't extract full category fields value.
    You need to prepare two tables with full category values(part, month), then you can use them with measure formula to generate an expanded matrix visual with correspond result.

    BTW, if you only want a new table with full records mapping, you can try to use cross join function to achieve your requirement:

    Table =
    ADDCOLUMNS (
        CROSSJOIN ( VALUES ( 'Sample'[Part] ), VALUES ( 'Sample'[Month] ) ),
        "Amount", CALCULATE (
            SUM ( 'Sample'[Amount] ),
            FILTER (
                ALLSELECTED ( 'Sample' ),
                [Month] = EARLIER ( 'Sample'[Month] )
                    && [Part] = EARLIER ( 'Sample'[Part] )
            )
        ) + 0
    )
    

    Regards,

    Xiaoxin Sheng