Need help in DAX for Monthly delta

[Srinivas904]

Hi, I am using a measure to calculate YTD Pricing. Based on this measure, I have a requirement for calculating the monthly delta. For example, in January, the value will be the same. When we go to February, the value should be the difference between February and January (i.e., February - January = February's value). Similarly, for March, it should be the difference between March and February (i.e., March - February = March's value), and so on. When we sum up all these monthly deltas, the total should be the same as the YTD Pricing value.

We are using a 4-4-5 custom calendar. Can anyone help with this?

YTD Pricing Total =

VAR DirectUK_Equipment_Table =

    FILTER(

        Fact_Transactions,

        Fact_Transactions[Business_Category] = "DIRECT - UK" &&

        Fact_Transactions[Accounts_Level_1] = "Equipment"

    )

VAR Other_Table =

    FILTER(

        Fact_Transactions,

        NOT (

            Fact_Transactions[Business_Category] = "DIRECT - UK" &&

            Fact_Transactions[Accounts_Level_1] = "Equipment"

        )

    )

VAR DirectUK_Equipment_Value =

    SUMX(

        SUMMARIZE(

            DirectUK_Equipment_Table,

            Fact_Transactions[Product]

        ),

        IF(

            ISBLANK([Prior Year Qty]) || [Prior Year Qty] = 0 ||

            ISBLANK([Volume_V1]) || [Volume_V1] = 0,

            0,

            ([ASP_V1] - [Prior_Year_ASP]) * [Volume_V1]

        )

    )

VAR Other_Value =

    SUMX(

        SUMMARIZE(

            Other_Table,

            Fact_Transactions[Product],

            Fact_Transactions[Customer_name]

        ),

        IF(

            ISBLANK([Prior Year Qty]) || [Prior Year Qty] = 0 ||

            ISBLANK([Volume_V1]) || [Volume_V1] = 0,

            0,

            ([ASP_V1] - [Prior_Year_ASP]) * [Volume_V1]

        )

    )

RETURN

DirectUK_Equipment_Value + Other_Value

need output in this way 

Thanks in Advance

Srinivas

[DataNinja777]

Hi @Srinivas904 ,

To calculate the monthly delta based on your existing YTD Pricing Total measure, you need to create a new measure that finds the difference between the current month's YTD and the previous month's YTD. In Power BI, this can be done by referencing the current date context and identifying the previous date based on your custom calendar. The trick is to ensure you’re working with the actual dates rather than just the month names, especially since your calendar is custom (4-4-5).

You can define the measure as follows:

Monthly Delta Pricing = 
VAR CurrentDate = MAX('Date'[Date])
VAR PrevDate =
    CALCULATE(
        MAX('Date'[Date]),
        FILTER(
            ALL('Date'),
            'Date'[Date] < CurrentDate
        )
    )
VAR YTD_Current =
    CALCULATE(
        [YTD Pricing Total],
        'Date'[Date] = CurrentDate
    )
VAR YTD_Previous =
    CALCULATE(
        [YTD Pricing Total],
        'Date'[Date] = PrevDate
    )
RETURN
    IF(
        ISBLANK(YTD_Previous),
        YTD_Current,
        YTD_Current - YTD_Previous
    )

This measure works by capturing the current date context from your calendar table and calculating the maximum date less than that to find the previous date. Then, it retrieves the YTD Pricing values for both dates using your existing YTD Pricing Total measure. Finally, it returns the difference, which represents the delta for that specific month. For the first month, where no previous period exists, it simply returns the YTD value as-is. This ensures that when summed over the months, the total of these monthly deltas matches the final YTD total.

Best regards,

[Srinivas904 · Accepted solution]

Hi @DataNinja777 , 

Thanks for your reply. I tried your approach, but the values didn’t come out correctly. However, I’ve managed to achieve the result I wanted using my own version of the measure — I’ve attached it below.

In the measure, I used the CurrentMonthFlag because it ensures the data is displayed only up to the current month according to my 4-4-5 fiscal calendar.

Monthly_Delta =
VAR CurrentMonth = MAX('DIM_TIME'[FIN_MONTH])
VAR SelectedYear = SELECTEDVALUE(DIM_TIME[FIN_YEAR]) -- Get the selected fiscal year
VAR YTD_Current = [YTD Pricing Total]  -- YTD value for the current month

-- Calculate YTD for the previous month, but make sure we respect the custom calendar year
VAR YTD_Previous =
    CALCULATE(
        [YTD Pricing Total],
        FILTER(
            ALL('DIM_TIME'),
            'DIM_TIME'[FIN_MONTH] = CurrentMonth - 1 &&
            'DIM_TIME'[FIN_YEAR] = SelectedYear -- Ensure it's within the same fiscal year
        )
    )

-- Now, adjust to calculate based on the custom fiscal month without showing for the previous fiscal month (March if we're in April)
RETURN
    IF(
        CurrentMonth = 1,
        YTD_Current,  -- For January, return YTD value directly (no previous month)
        IF(
            SELECTEDVALUE('DIM_TIME'[CurrentMonthFlag]) = 1,
            YTD_Current - YTD_Previous,  -- For other months, calculate the difference
            BLANK()  -- If the current month is not flagged as part of the current period, return blank
        )
    )

Thanks

Srinivas