Help with DAX measure - Dynamically recalculated target

[Rai_Lomarques]

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, id_Custumer, id_Seller, id_CustumerPortifolio, Qt, Sellin $;

Goals fact table called 'fGoals' containing the fields Date, id_Product, id_Seller, id_CustumerPortifolio, Qt, Goal $;

Product dimension table called 'dProduct' containing the fields id_Product, Desc_Product and id_Category. The 'dProduct' table is related to 'fSales' and 'fGoals' by the id_Product field and to 'dCategorys' by the id_Category field

Category dimension table called 'dCategory' containing the fields id_Category, Category_desc, sub_Category1 and sub_Category2;

Date dimension table called 'dCalendar' containing the fields Date, Month_Name, Month_Number, Year, Year/Month in the format "MM/YYYY", Month_Index in the format "YYYYMM". This table is related to 'fSales' and 'fGoals' by the Date field;

Date dimension table called 'dCalendar (Aux)' containing the fields Date, Month_Name, Month_Number, Year, Year/Month in the format "MM/YYYY", Month_Index in the format "YYYYMM". This table has an inactive relationship with 'dCalendar' by the date field and serves as an auxiliary data table.

Salesperson dimension table called 'dSellers' containing the fields id_Seller, Seller_Name, Supervisor_Name, Maneger_Name. This table is related to 'fSales' and 'fGoals' by the id_Seller field;

Customer dimension table called 'dCustumer' containing the fields id_Custumer, Custumer_Desc, City, UF, id_Seller. This table is related to 'fSales' by the id_Custumer field and to 'dSellers' by the id_Seller field

I need to create a DAX measure that dynamically calculates the target value considering the gap not reached in previous periods. If the target for a month was not reached, the gap should be taken and distributed to the subsequent months considering the weight that each month has for the target.

Example:

Suppose the current month is February 2024.

The goal for the first quarter of 2024 is $3,000,000, where:

The goal for January/2024 is $1,000,000
The goal for February/2024 is $900,000
The goal for March/2024 is $1,100,000

The January/2024 achievement was $950,000
The February/2024 achievement is still in progress, since it is the current month.
The March/2024 achievement has not yet occurred.

Considering that January/2024 has already passed and the target was not reached, leaving a gap of $50,000, this gap should be taken and applied to the following months within the current quarter, in this case the current month February/2024 and the next month within the quarter March/2024.

Considering that the target for February/2024 and March/2024 added together is $2,000,000, the weight that each of these two months has should be calculated. In this case, the weight for February/2024 is 45% and the weight for March/2024 is $55%, therefore the new target for February/2024 is $900,000 + $22,500 corresponding to 45% of the gap of $50,000, totaling $922,500. The new target for March/2024 is $1,100,000 + $27,500 corresponding to 55% of the $50,000 gap, totaling $1,127,500.

Assuming that the first quarter has ended and the current month is April/2024, that is, it is the second quarter, then the gap not reached from the first quarter target should be taken and added to the months of the second quarter proportionally, calculating the weight that each month of the second quarter has for the total target of the second quarter and so on until the end of the year.

I do not consider the target from previous years. The settings should only occur within the year in context.

I made the DAX measure below what is working in parts. It is returning the gap from the previous month to the month in context, however it is necessary to divide the target for the 2 months belonging to the same quarter.

Goal Rollout = 
VAR DateCurrent = MAX('dCalendar'[Date])
VAR YearCurrent = YEAR(DateCurrent)
VAR MonthCurrent = MONTH(DateCurrent)

VAR MonthPrevious = MonthCurrent - 1
VAR YearMonthPrevious = IF(MonthPrevious = 0, YearCurrent - 1, YearCurrent)
VAR SalesMonthPrevious = 
    IF(
        MonthPrevious >= 1,
        CALCULATE(
            [Sellin $] - [Goal $],
            FILTER(
                ALL('dCalendar'),
                YEAR('dCalendar'[Date]) = YearCurrent && 
                MONTH('dCalendar'[Date]) = MonthPrevious
            )
        ),
        0 
    )

RETURN SalesMonthPrevious

[v-hashadapu]

Hi @Rai_Lomarques , We are closing this thread as we haven't heard from you in a while, according to our follow-up policy. If you have any more questions, please start a new thread on the Microsoft Fabric Community Forum. We will be happy to assist you! Thank you for being part of the community!

[v-hashadapu]

Hi @Rai_Lomarques , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

[v-hashadapu]

Hi @Rai_Lomarques , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

[v-hashadapu]

Hi @Rai_Lomarques , Thank you for reaching out to the Microsoft Community Forum.

Please try below:
Goal Rollout =

VAR DateCurrent = MAX('dCalendar'[Date])

VAR YearCurrent = YEAR(DateCurrent)

VAR MonthCurrent = MONTH(DateCurrent)

VAR QuarterCurrent = QUARTER(DateCurrent)

VAR PriorMonths =

    FILTER(

        ALL('dCalendar'),

        YEAR('dCalendar'[Date]) = YearCurrent &&

        'dCalendar'[Date] < DateCurrent

    )

VAR TotalGoalPrior = CALCULATE([Goal $], PriorMonths)

VAR TotalSalesPrior = CALCULATE([Sellin $], PriorMonths)

VAR CumulativeGap = MAX(0, TotalGoalPrior - TotalSalesPrior)

VAR RemainingMonths =

    FILTER(

        ALL('dCalendar'),

        YEAR('dCalendar'[Date]) = YearCurrent &&

        QUARTER('dCalendar'[Date]) = QuarterCurrent &&

        MONTH('dCalendar'[Date]) >= MonthCurrent

    )

VAR RemainingTarget = CALCULATE([Goal $], RemainingMonths)

VAR CurrentMonthTarget =

    CALCULATE(

        [Goal $],

        FILTER(

            RemainingMonths,

            MONTH('dCalendar'[Date]) = MonthCurrent

        )

    )

VAR Weight = DIVIDE(CurrentMonthTarget, RemainingTarget, 0)

VAR Result = CurrentMonthTarget + (CumulativeGap * Weight)

RETURN Result

This calculates the full year-to-date deficit (e.g., January’s $50,000 gap), then splits it proportionally (45%/55%) across February and March, yielding $922,500 and $1,127,500 as you specified.

If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

[timalbers]

I 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

[timalbers]

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]

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.