Forum Discussion
How to add zero values for missing sales months so the data is evenly spaced for forecasting?
- Anonymous6 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 ) ) + 0If 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
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