Skip to content

Instantly share code, notes, and snippets.

@ImkeF
Created May 28, 2022 18:02
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save ImkeF/fb751fd1c92e3897c7ad9773332b1995 to your computer and use it in GitHub Desktop.
Save ImkeF/fb751fd1c92e3897c7ad9773332b1995 to your computer and use it in GitHub Desktop.
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