Trouble with 12 Month Moving average

[Anonymous]

Hi,

I;m having trouble figuring out how to do 12 month moving averages.

My Model looks like this:

So users have onservations on specific dates and they can enter a nimber of scores for each observation.

Note that the relationship between Date and Observation Date cannot be mad active because it will break other measures in my report.

So I created these measures to show the count of scores, the total of the scores and the  Average of the scores by month:

ScoreCount = CALCULATE(

COUNTROWS(Scores),

USERELATIONSHIP(Observations[observationdate],Dates[Date])

)

TotalScores =

CALCULATE(

Sum(Scores[score]),

USERELATIONSHIP(Observations[observationdate],Dates[Date])

)

AverageScore =

[TotalScores]

/

[ScoreCount]

So far so good:

Now i am trying to keep it simple and just do a two  month rolling average, so the Feb numbers should conolidate the Jan and Feb numbers, Mar should consolidate feb and Mar).

DId some googling on this and avideo was telling me to do something like this:

Rolling2ScoreCount =

var MaxDate=CALCULATE(MAX(Dates[Date]),USERELATIONSHIP(Observations[observationdate],Dates[Date]))

return

CALCULATE(

COUNTROWS(Scores),

Filter(

ALL(Observations),

AND(

Observations[observationdate] <=MaxDate,

Observations[observationdate] > DATEADD(

))

but that  demo had an active relationship  and min doesnt.

Anyone know how i can calulate the rolling 2 month average without an active relationship?

(link to pbx on github attached)

TIA

Russell

https://github.com/russgove/pbi/blob/main/RollingAverageUserRelationship.pbix 

[Anonymous]

**bleep**, after writing all this out the answe dawned on me :

Rolling2ScoreCount =

var MaxDate=CALCULATE(MAX(Dates[Date]),USERELATIONSHIP(Observations[observationdate],Dates[Date]))

return

var MinDate=CALCULATE(MAX(Dates[Date]),

USERELATIONSHIP(Observations[observationdate],Dates[Date]),

DATEADD(Dates[Date],-2,MONTH)

)

return

CALCULATE(

COUNTROWS(Scores),

Filter(

ALL(Observations),

AND(

Observations[observationdate] <=MaxDate,

Observations[observationdate] > MinDate

)))

is there a better way to do this?

[CNENFRNL]

I don't see the point deactivating the relastionship between date table and Observations. Whatever, simple enough to author the measures,

R2M Count = 
CALCULATE(
    [ScoreCount],
    DATESINPERIOD( Dates[Date], MAX( Dates[Date] ), -2, MONTH )
)

R2M Total = 
CALCULATE(
    [TotalScores],
    DATESINPERIOD( Dates[Date], MAX( Dates[Date] ), -2, MONTH )
)