Need help with Rolling 3 months

Maveric — Fri, 13 Oct 2023 09:15:37 GMT

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

)

Re: Need help with Rolling 3 months

Anonymous — Tue, 17 Oct 2023 07:53:57 GMT

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.

topic Re: Need help with Rolling 3 months in DAX Commands and Tips

Need help with Rolling 3 months

Re: Need help with Rolling 3 months