Forum Discussion
Rai_Lomarques
Helper II
1 year agoHelp with DAX measure - Dynamically recalculated target
Hello friends, I need help creating a DAX measure. Below is a list of all the tables and relationships in my data model: Sales fact table called 'fSales' containing the fields Date, id_Product, ...
timalbers
Skilled Sharer
1 year agoI really hope this one works, because it is getting a little complex.
Just let me know, if you could achieve ist or there still needs to be some adjustments.
Goal Rollout =
VAR DateCurrent = MAX('dCalendar'[Date])
VAR YearCurrent = YEAR(DateCurrent)
VAR MonthCurrent = MONTH(DateCurrent)
VAR QuarterCurrent = QUARTER(DateCurrent)
VAR MonthPrevious = MonthCurrent - 1
VAR YearMonthPrevious = IF(MonthPrevious = 0, YearCurrent - 1, YearCurrent)
VAR SalesMonthPrevious =
IF(
MonthPrevious >= 1,
CALCULATE(
[Sellin R$] - [Meta R$],
FILTER(
ALL('dCalendar'),
YEAR('dCalendar'[Date]) = YearCurrent &&
MONTH('dCalendar'[Date]) = MonthPrevious
)
),
0
)
VAR GapMonthPrevious = MAX( 0, -SalesMonthPrevious ) -- Only keep deficits, ignore surpluses
-- Get the previous quarter's data
VAR QuarterPrevious = IF( QuarterCurrent - 1 = 0, 4, QuarterCurrent - 1 )
VAR QuarterPreviousMonths =
FILTER(
ALL('dCalendar'),
YEAR('dCalendar'[Date]) = YearCurrent &&
QUARTER('dCalendar'[Date]) = QuarterPrevious
)
-- Calculate previous quarters total gap
VAR PrevQuarterGoal = CALCULATE( [Meta R$], QuarterPreviousMonths )
VAR PrevQuarterSales = CALCULATE( [Sellin R$], QuarterPreviousMonths )
VAR PrevQuarterGap = MAX( 0, PrevQuarterGoal - PrevQuarterSales )
-- If in the last month of a quarter, add the current quarters gap to redistribute
VAR CurrentQuarterTarget =
CALCULATE(
[Meta R$],
FILTER(
ALL('dCalendar'),
YEAR('dCalendar'[Date]) = YearCurrent &&
QUARTER('dCalendar'[Date]) = QuarterCurrent &&
MONTH('dCalendar'[Date]) < MonthCurrent
)
)
VAR CurrentQuarterSales =
CALCULATE(
[Sellin R$],
FILTER(
ALL('dCalendar'),
YEAR('dCalendar'[Date]) = YearCurrent &&
QUARTER('dCalendar'[Date]) = QuarterCurrent &&
MONTH('dCalendar'[Date]) < MonthCurrent
)
)
VAR CurrentQuarterGap = MAX( 0, CurrentQuarterTarget - CurrentQuarterSales )
-- Total gap to distribute
VAR TotalGap =
IF(
MonthCurrent IN {3, 6, 9, 12}, -- last month of the quarter
PrevQuarterGap + CurrentQuarterGap,
PrevQuarterGap
) + GapMonthPrevious -- add the previous months gap
-- If it's Q4, do not carry the gap into the next year
VAR FinalGap = IF(QuarterCurrent = 4, 0, TotalGap)
VAR RemainingMonths =
FILTER(
ALL('dCalendar'),
YEAR('dCalendar'[Date]) = YearCurrent &&
QUARTER('dCalendar'[Date]) = QuarterCurrent &&
MONTH('dCalendar'[Date]) >= MonthCurrent
)
VAR RemainingTarget =
CALCULATE(
[Meta R$],
RemainingMonths
)
VAR CurrentMonthTarget =
CALCULATE(
[Meta R$],
FILTER(
RemainingMonths,
'dCalendar'[Month_Number] = MonthCurrent
)
)
VAR Weight = DIVIDE( CurrentMonthTarget, RemainingTarget, 0 )
VAR Result = CurrentMonthTarget + ( FinalGap * Weight )
RETURN Result