Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Goal: Calculate the Forecast and Sales = sum of the current month, previous month, and next month.
1 - This calculation must consider that we can have different products in the dataset, and this calculation needs to be done by the product.
2 - It is needed to consider in the dataset there might be some months missing (without sales and forecast) but the formula should consider the missing months as "0".
Example: First line of the sample dataset "Product 2". As we don't have Jan.2022, it is only the sum of Feb and Mar/2022.
3 - I need this calculation to be done inside the table (as a column calculated) for specific reasons of the business, working in an aggregation way directly in a measure, won't work, because obviously one product will offset another one (in a DEVIATION = ABS (Sales (Quarter) - Forecast (Quarter)) column that I intend to calculate later) and it is not a result expected using measures.
Solved! Go to Solution.
To make it easier, I included the table in values over here. Thanks!
Start of Month | SKUID | Forecast | Sales | Forecast (Quarter) | Sales (Quarter) | |
01-Jan-22 | Product 1 | 6500 | 750 | 6,950 | 1,025 | |
01-Feb-22 | Product 1 | 450 | 275 | 7,330 | 2,026 | |
01-Mar-22 | Product 1 | 380 | 1001 | 1,505 | 1,276 | |
01-Apr-22 | Product 1 | 675 | 1,078 | 1,124 | ||
01-Jul-22 | Product 1 | 23 | 122.5 | 8,786 | 20,757 | |
01-Aug-22 | Product 1 | 8088 | 20634 | 69,716 | 44,608 | |
01-Sep-22 | Product 1 | 61604.5 | 23851.5 | 112,101 | 86,744 | |
01-Oct-22 | Product 1 | 42408.5 | 42258 | 139,564 | 98,266 | |
01-Nov-22 | Product 1 | 35551 | 32156.5 | 91,470 | 84,792 | |
01-Dec-22 | Product 1 | 13510 | 10377 | 49,236 | 42,602 | |
01-Jan-23 | Product 1 | 175 | 68 | 13,933 | 11,259 | |
01-Feb-23 | Product 1 | 247.5 | 814 | 803 | 882 | |
01-Mar-23 | Product 1 | 380 | 1,143 | 1,329 | ||
01-Apr-23 | Product 1 | 515 | 515 | 1,580 | 838 | |
01-May-23 | Product 1 | 685 | 322.5 | 5,135 | 3,538 | |
01-Jun-23 | Product 1 | 3935 | 2700 | 4,970 | 3,023 | |
01-Jul-23 | Product 1 | 350 | 24,866 | 2,700 | ||
01-Aug-23 | Product 1 | 20581 | 57,486 | 0 | ||
01-Sep-23 | Product 1 | 36555 | 94,249 | 0 | ||
01-Oct-23 | Product 1 | 37113 | 127,384 | 0 | ||
01-Nov-23 | Product 1 | 53716 | 91,204 | 0 | ||
01-Dec-23 | Product 1 | 375 | 54,091 | 0 | ||
01-Feb-22 | Product 2 | 900 | 550 | 1,660 | 2,552 | |
01-Mar-22 | Product 2 | 760 | 2002 | 3,010 | 2,552 | |
01-Apr-22 | Product 2 | 1350 | 2,156 | 2,247 | ||
01-Jul-22 | Product 2 | 46 | 245 | 17,572 | 41,513 | |
01-Aug-22 | Product 2 | 16176 | 41268 | 139,431 | 89,216 | |
01-Sep-22 | Product 2 | 123209 | 47703 | 224,202 | 173,487 | |
01-Oct-22 | Product 2 | 84817 | 84516 | 279,128 | 196,532 | |
01-Nov-22 | Product 2 | 71102 | 64313 | 182,939 | 169,583 | |
01-Dec-22 | Product 2 | 27020 | 20754 | 98,472 | 85,203 | |
01-Jan-23 | Product 2 | 350 | 136 | 27,865 | 22,518 | |
01-Feb-23 | Product 2 | 495 | 1628 | 1,605 | 1,764 | |
01-Mar-23 | Product 2 | 760 | 2,285 | 2,658 | ||
01-Apr-23 | Product 2 | 1030 | 1030 | 3,160 | 1,675 | |
01-May-23 | Product 2 | 1370 | 645 | 10,270 | 7,075 | |
01-Jun-23 | Product 2 | 7870 | 5400 | 9,940 | 6,045 | |
01-Jul-23 | Product 2 | 700 | 49,732 | 5,400 | ||
01-Aug-23 | Product 2 | 41162 | 114,972 | 0 | ||
01-Sep-23 | Product 2 | 73110 | 188,498 | 0 | ||
01-Oct-23 | Product 2 | 74226 | 254,768 | 0 | ||
01-Nov-23 | Product 2 | 107432 | 182,408 | 0 | ||
01-Dec-23 | Product 2 | 750 | 108,182 | 0 |
HI @Jorge_Lacerda,
I'd like to suggest you to extract the current SKUID and date as condition to filter on table records and get result:
Forecast(quarter)=
CALCULATE (
SUM ( Table1[Forecast] ),
FILTER (
Table1,
VAR currDate =
EARLIER ( Table1[Start of Month] )
RETURN
[SKUID] = EARLIER ( Table1[SKUID] )
&& [Start of Month]
>= DATE ( YEAR ( currDate ), MONTH ( currDate ) - 1, DAY ( currDate ) )
&& [Start of Month]
<= DATE ( YEAR ( currDate ), MONTH ( currDate ) + 1, DAY ( currDate ) )
)
)
Sales(quarter) =
VAR _calendar =
FILTER (
CALENDAR (
DATE ( YEAR ( Table1[Start of Month] ), MONTH ( Table1[Start of Month] ) - 1, 1 ),
DATE ( YEAR ( Table1[Start of Month] ), MONTH ( Table1[Start of Month] ) + 1, 1 )
),
DAY ( [Date] ) = 1
)
VAR temp =
ADDCOLUMNS (
_calendar,
"SKUID", Table1[SKUID],
"Rolling",
LOOKUPVALUE (
Table1[Sales],
Table1[SKUID], [SKUID],
Table1[Start of Month], [Date],
0
)
)
RETURN
SUMX ( temp, [Rolling] ) + 0
For the not existed date ranges, I create a temp calendar table with complete ranges and manually fill these blank parts to zero.
Regards,
Xiaoxin Sheng
To make it easier, I included the table in values over here. Thanks!
Start of Month | SKUID | Forecast | Sales | Forecast (Quarter) | Sales (Quarter) | |
01-Jan-22 | Product 1 | 6500 | 750 | 6,950 | 1,025 | |
01-Feb-22 | Product 1 | 450 | 275 | 7,330 | 2,026 | |
01-Mar-22 | Product 1 | 380 | 1001 | 1,505 | 1,276 | |
01-Apr-22 | Product 1 | 675 | 1,078 | 1,124 | ||
01-Jul-22 | Product 1 | 23 | 122.5 | 8,786 | 20,757 | |
01-Aug-22 | Product 1 | 8088 | 20634 | 69,716 | 44,608 | |
01-Sep-22 | Product 1 | 61604.5 | 23851.5 | 112,101 | 86,744 | |
01-Oct-22 | Product 1 | 42408.5 | 42258 | 139,564 | 98,266 | |
01-Nov-22 | Product 1 | 35551 | 32156.5 | 91,470 | 84,792 | |
01-Dec-22 | Product 1 | 13510 | 10377 | 49,236 | 42,602 | |
01-Jan-23 | Product 1 | 175 | 68 | 13,933 | 11,259 | |
01-Feb-23 | Product 1 | 247.5 | 814 | 803 | 882 | |
01-Mar-23 | Product 1 | 380 | 1,143 | 1,329 | ||
01-Apr-23 | Product 1 | 515 | 515 | 1,580 | 838 | |
01-May-23 | Product 1 | 685 | 322.5 | 5,135 | 3,538 | |
01-Jun-23 | Product 1 | 3935 | 2700 | 4,970 | 3,023 | |
01-Jul-23 | Product 1 | 350 | 24,866 | 2,700 | ||
01-Aug-23 | Product 1 | 20581 | 57,486 | 0 | ||
01-Sep-23 | Product 1 | 36555 | 94,249 | 0 | ||
01-Oct-23 | Product 1 | 37113 | 127,384 | 0 | ||
01-Nov-23 | Product 1 | 53716 | 91,204 | 0 | ||
01-Dec-23 | Product 1 | 375 | 54,091 | 0 | ||
01-Feb-22 | Product 2 | 900 | 550 | 1,660 | 2,552 | |
01-Mar-22 | Product 2 | 760 | 2002 | 3,010 | 2,552 | |
01-Apr-22 | Product 2 | 1350 | 2,156 | 2,247 | ||
01-Jul-22 | Product 2 | 46 | 245 | 17,572 | 41,513 | |
01-Aug-22 | Product 2 | 16176 | 41268 | 139,431 | 89,216 | |
01-Sep-22 | Product 2 | 123209 | 47703 | 224,202 | 173,487 | |
01-Oct-22 | Product 2 | 84817 | 84516 | 279,128 | 196,532 | |
01-Nov-22 | Product 2 | 71102 | 64313 | 182,939 | 169,583 | |
01-Dec-22 | Product 2 | 27020 | 20754 | 98,472 | 85,203 | |
01-Jan-23 | Product 2 | 350 | 136 | 27,865 | 22,518 | |
01-Feb-23 | Product 2 | 495 | 1628 | 1,605 | 1,764 | |
01-Mar-23 | Product 2 | 760 | 2,285 | 2,658 | ||
01-Apr-23 | Product 2 | 1030 | 1030 | 3,160 | 1,675 | |
01-May-23 | Product 2 | 1370 | 645 | 10,270 | 7,075 | |
01-Jun-23 | Product 2 | 7870 | 5400 | 9,940 | 6,045 | |
01-Jul-23 | Product 2 | 700 | 49,732 | 5,400 | ||
01-Aug-23 | Product 2 | 41162 | 114,972 | 0 | ||
01-Sep-23 | Product 2 | 73110 | 188,498 | 0 | ||
01-Oct-23 | Product 2 | 74226 | 254,768 | 0 | ||
01-Nov-23 | Product 2 | 107432 | 182,408 | 0 | ||
01-Dec-23 | Product 2 | 750 | 108,182 | 0 |
HI @Jorge_Lacerda,
I'd like to suggest you to extract the current SKUID and date as condition to filter on table records and get result:
Forecast(quarter)=
CALCULATE (
SUM ( Table1[Forecast] ),
FILTER (
Table1,
VAR currDate =
EARLIER ( Table1[Start of Month] )
RETURN
[SKUID] = EARLIER ( Table1[SKUID] )
&& [Start of Month]
>= DATE ( YEAR ( currDate ), MONTH ( currDate ) - 1, DAY ( currDate ) )
&& [Start of Month]
<= DATE ( YEAR ( currDate ), MONTH ( currDate ) + 1, DAY ( currDate ) )
)
)
Sales(quarter) =
VAR _calendar =
FILTER (
CALENDAR (
DATE ( YEAR ( Table1[Start of Month] ), MONTH ( Table1[Start of Month] ) - 1, 1 ),
DATE ( YEAR ( Table1[Start of Month] ), MONTH ( Table1[Start of Month] ) + 1, 1 )
),
DAY ( [Date] ) = 1
)
VAR temp =
ADDCOLUMNS (
_calendar,
"SKUID", Table1[SKUID],
"Rolling",
LOOKUPVALUE (
Table1[Sales],
Table1[SKUID], [SKUID],
Table1[Start of Month], [Date],
0
)
)
RETURN
SUMX ( temp, [Rolling] ) + 0
For the not existed date ranges, I create a temp calendar table with complete ranges and manually fill these blank parts to zero.
Regards,
Xiaoxin Sheng
@Xiaoxin Sheng, Thanks so much for your help!
The only thing I would like to ask you is if you have any technique to make the table with all dates of the calendar automatically from this dataset because it will be fundamental to get those measures calculated in line that don't have sales or forecast in a certain month but eventually have some numbers in previous or in one-month forward.
User | Count |
---|---|
90 | |
74 | |
67 | |
63 | |
55 |
User | Count |
---|---|
102 | |
93 | |
73 | |
60 | |
59 |