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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
meetrachhadia9
Regular Visitor

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, the forecast will either show up without considering the months or else the error will pop up saying the data is irregular to forecast. I know I have to add zero values for those parts for the missing months. The question is how to add zero values for such large number of parts so that the forecast shows up and is accurate?

The parts are around 50000 and the demand is monthly demand for 36 months. Also, the data editing needs to be minimal on Excel because this needs to be a real time based dashboard.

Does anyone know how to solve this problem? 

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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

 

13.png

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

2 REPLIES 2
v-shex-msft
Community Support
Community Support

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

 

13.png

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Greg_Deckler
Super User
Super User

Tough to say, are you using a measure? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.