Forum Discussion

Rai_Lomarques's avatar
Rai_Lomarques
Icon for Helper II rankHelper II
1 year ago

Help 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's avatar
timalbers
Icon for Skilled Sharer rankSkilled Sharer
1 year ago

Hi Rai_Lomarques 

I hope I got this information all right. But if so, there might be a chance this one works.

I commented a few things for a better understanding.

 

Goal Rollout = 
VAR DateCurrent = MAX('dCalendar'[Date])
VAR YearCurrent = YEAR(DateCurrent)
VAR MonthCurrent = MONTH(DateCurrent)
VAR QuarterCurrent = QUARTER(DateCurrent)  -- added quarter

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 
    )

-- Get the target for the remaining months in the current quarter
VAR RemainingMonths = 
    FILTER(
        ALL('dCalendar'),
        YEAR('dCalendar'[Date]) = YearCurrent &&
        QUARTER('dCalendar'[Date]) = QuarterCurrent &&
        MONTH('dCalendar'[Date]) >= MonthCurrent
    )

VAR RemainingTarget = 
    CALCULATE(
        [Meta R$],  -- assuming this is your target measure? Maybe I'm wrong...
        RemainingMonths
    )

-- Get the current month's weight for redistribution
VAR CurrentMonthTarget = 
    CALCULATE(
        [Meta R$],  -- again... maybe this needs to be adjusted
        FILTER(
            RemainingMonths, 
            'dCalendar'[Month_Number] = MonthCurrent
        )
    )
VAR Weight = DIVIDE( CurrentMonthTarget, RemainingTarget, 0 )

VAR Result = CurrentMonthTarget + ( SalesMonthPrevious * Weight ) -- applies proportional gap

RETURN Result

 
Maybe there still is a little adjustment necessary. Just let me know.

 

Cheers

Tim

  • Rai_Lomarques's avatar
    Rai_Lomarques
    Icon for Helper II rankHelper II
    1 year ago

    Thank you very much! We are on the right track. You understood correctly, "[Goal R$]" means [Goal $].

    Your measure is working correctly in parts, but I believe it is because I did not explain it correctly.

     

    The current quarter's goal needs to be added to the previous quarter's goal GAP if the 1st quarter's goal was not met. Of course, if the 1st quarter has a goal with a surplus, this difference does NOT need to be distributed to subsequent months.

     

    For example:
    If the 1st quarter is over and the goal was not met, the deficit needs to be redistributed to the 2nd quarter proportionally between the months following the same rule explained.

     

    If the current month is the 3rd month of the quarter, then it is necessary to add the unmet gap from the current quarter and the previous quarter.