Rolling N months growth %

[Hemant_Jaiswar]

Hello,
i am using SQLBI Rolling 6 months formula, you can that method here - https://www.youtube.com/watch?v=d8Rm7dwM6gc&t=1010s
Last 6 Months =

CALCULATE(

    [RRP],

    DATESINPERIOD(

        Dim_Date[Date],

        EOMONTH(MAX('Dim_Date Duplicate'[Date]), -1),  -- Exclude the max (latest) month by using EOMONTH

        -6, MONTH   -- Get the last 6 months (from the month before the max month),

    ),

    KEEPFILTERS(Dim_Date),

    REMOVEFILTERS('Dim_Date Duplicate'),

    USERELATIONSHIP(Dim_Date[Date], 'Dim_Date Duplicate'[Date])

    )
and accordingly
Sameperiodlastyear = 

CALCULATE(

    [RRP],

    SAMEPERIODLASTYEAR(

        DATESINPERIOD(

            Dim_Date[Date],

            EOMONTH(MAX('Dim_Date Duplicate'[Date]), -1),  -- Exclude the max (latest) month by using EOMONTH

            -6, MONTH   -- Get the last 6 months (from the month before the max month)

        )

    ),

    KEEPFILTERS(Dim_Date),

    REMOVEFILTERS('Dim_Date Duplicate'),

    USERELATIONSHIP(Dim_Date[Date], 'Dim_Date Duplicate'[Date])

)
Expected result

Oct-24	15	12
Nov-24	50	26
Dec-24	40	30
Jan-25	45	40
Feb-25	34	42
Mar-25	20	30

Result i am getting is 

23-Oct		12
23-Nov		26
23-Dec		30
Jan-24		40
Feb-24		42
Mar-24		30
Oct-24	15
Nov-24	50
Dec-24	40
Jan-25	45
Feb-25	43
Mar-25	20

[rohit1991]

Hi @Hemant_Jaiswar ,

Based on your scenario and the discrepancy you're seeing between the expected and actual results, the issue likely stems from how SAMEPERIODLASTYEAR() handles dynamic rolling periods. It doesn’t always align correctly with rolling windows, especially when you're excluding the latest month and working with complex date relationships. Instead of using SAMEPERIODLASTYEAR, try manually shifting the window back by 12 months using EOMONTH(MAX('Dim_Date Duplicate'[Date]), -13) to define the start of the period for the previous year.

This method gives you direct control over the time frame and ensures that the same 6-month rolling window is applied to the prior year, improving alignment and accuracy. Bhanu Gautam's suggestion reflects this approach, and it's often more reliable in scenarios involving custom rolling date logic. Give that a try and see if the results match your expectations.

[v-veshwara-msft]

Hi @Hemant_Jaiswar ,

Thanks for reaching out to Microsoft Fabric Community.

Just checking in to see if your issue has been resolved by now. If not, please consider the following:

For calculating the rolling 6-month growth and its same period last year value while excluding the latest month, using a manual 12-month shift to align the rolling window usually resolves the misalignment issues seen with SAMEPERIODLASTYEAR() on rolling periods.

Hope this helps. Please reach out for further assistance.

If you have found another solution that works, please share it here to help others.

Also, if any response helped you, kindly mark it as the accepted solution and consider giving kudos to assist others with similar queries.

Thanks,

[Hemant_Jaiswar]

Not giving the expected result.

[bhanu_gautam]

@Hemant_Jaiswar Try using

Sameperiodlastyear =
CALCULATE(
[RRP],
DATESINPERIOD(
Dim_Date[Date],
EOMONTH(MAX('Dim_Date Duplicate'[Date]), -13), -- Move back 13 months to get the same period last year
-6, MONTH -- Get the last 6 months (from the month before the max month)
),
KEEPFILTERS(Dim_Date),
REMOVEFILTERS('Dim_Date Duplicate'),
USERELATIONSHIP(Dim_Date[Date], 'Dim_Date Duplicate'[Date])
)