Need help with Rolling 3 months

[Maveric]

Hi All,

New to PowerBI DaX

Trying to get the rolling 3 months spent for a Financial year (july to june) by month FY filtered by monthly snapshot(from Dim_snapshots table) . 

Tables used as below 

Fact Finance

Dim Date 

Dim Snapshots

Dim relative to snapshots

My current dax is below which works if I select a month in the table but doest not sync month with selected snapshot . ( if snapshot is Dec 2022 I want the rolling total to be from Dec 2022 to October 2022. 

Rolling 3 Months =

VAR MaxDate = MAX('Dim_Date'[Date])

VAR MaxMonthNumber = MAX('Dim_Date'[Period])

RETURN CALCULATE(

    SUM('Fact_Financials'[Actuals]),

    FILTER(

        ALL('Dim_Date'),

        'Dim_Date'[Date] >= DATE(YEAR(MaxDate), MONTH(MaxDate) - 2, 1) &&

        'Dim_Date'[Date] <= MaxDate &&

        'Dim_Date'[Period] >= MaxMonthNumber - 2 &&

        'Dim_Date'[Period] <= MaxMonthNumber

    )

)

[Anonymous]

Hi @Maveric,

I think you can try to create a relationship between [Date] column in snapshot table and DimDate table. Or you can try to create your measure by the [Date] column in snapshot table.

Your issue should be caused by your data model. Please share a sample file with me and show me a screenshot with the result you want.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.