Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Simple DAX forecast distribution with seasonality: https://wp.me/p6lgsG-2tS
SumForecast =
VAR _previousMonth =
CALCULATE (
MAX ( 'Date'[Year Month Number] ), -- Must be the column that is connected to the Forecast-table
REMOVEFILTERS ( 'Date' ),
'Date'[Date] = TODAY ()
) - 1
VAR _latestYTD =
CALCULATE (
[YTD SumSales],
REMOVEFILTERS ( 'Date' ),
'Date'[Year Month Number] = _previousMonth
)
VAR _cumulPercentage =
CALCULATE (
[YTD SumPercentage],
REMOVEFILTERS ( 'Date' ),
'Date'[Year Month Number] = _previousMonth
)
VAR _amountForTotalYear =
DIVIDE ( _latestYTD, _cumulPercentage )
VAR _result =
SUMX (
'Forecast',
VAR _isPlanningMonth =
( CALCULATE ( MAX ( 'Date'[Year Month Number] ) ) > _previousMonth )
VAR _relevantYTD =
IF ( _isPlanningMonth, _amountForTotalYear * Forecast[ % Forecast], [SumSales] )
RETURN
_relevantYTD
)
RETURN
_result
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment