Calculating Rolling AVG 30days in directquery mode

[Anonymous]

Hi everyone,

I am not sure my rolling average calculation is working properly it brings up blank columns in a table

so please may I ask for help with this to see where I am going wrong?

I have two tables in directquery mode;

Table1: SPROC Snapshot

Table2: DimTable

I joined DimTable[Date] column on to SPROC Snapshot[ONP DateTime] column to create a 1:many relationship - could this be the issue? that creating a relationship needs to be date only columns (no timestamps).

I used the below calculation for Average duration and made a measure:

Rolling avg (30days) = CALCULATE(AVERAGEX('SPROC Snapshot', [duration_seconds]),DATESINPERIOD(DimDate[Date] ,LASTDATE(DimDate[Date]) , -30, DAY ))

Any help would be appreciated as I am a beginner in creating relationships in PBx

[davehus · Accepted solution]

Hi @Anonymous ,

Reference the date in the snapshot and not the date table in the DATESINPERIOD and that should work for you.

Did I help you today? Please accept my solution and hit the Kudos button.