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.
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] ) + 0For 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
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] ) + 0For 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
Helpful resources
Microsoft Fabric Learn Together
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
