Rolling 6 month Average

[Sirona_Kis]

Hi all,

I am trying to create a rolling averages, in some cases its working but for some it isn't. Let me explain

The base dax for count is as below:

Count =

Var A =

CALCULATE (DISTINCTCOUNT (Table[ClaimNo.]),

Status IN {"OF", "RO"},

Table[OpenDate]<= MAX(CALENDAR[Date])

Var B =

CALCULATE (DISTINCTCOUNT (Table[ClaimNo.]),

Status IN {"ON"},

Table[Closedate]>= MAX(CALENDAR[Date])

Return

A+B

As above the count dax , I have created total paid dax as well 

Now when I try to use these in another dax for rolling average it is not counting average of rest 5 months but is calculating for a single month 

Following is the dax I am using for rolling 6month average

6M AVG =

Var A = CALCULATE ( [Count],

DATESINPERIOD(CALENDAR[Date],

MAX ( CALENDAR[Date]),

-6,

Month))

Var B = CALCULATE ( [Total Paid],

DATESINPERIOD(CALENDAR[Date],

MAX ( CALENDAR[Date]),

-6,

Month))

Return

DIVIDE (B, A, 0)

This is just giving me month wise calculation and not rolling 6 months value

Can you please suggest what can be done for this?

There is no active relationship between Table and CALENDAR 

Thanks in advance

[sergej_og · Accepted solution]

Hey @Sirona_Kis 
is there any reason why there is no active relationship between Table and Calendar?
Could be a bit challanging to make a time intelligence calculation.

Have you tried to use AVERAGEX() to get your rolling avg?
Like this:

Formula used:

Rolling Avg = 
VAR Period = 
    DATESINPERIOD(
        'Calendar'[Date],
        MAX('Calendar'[Date]),
        -6,
        MONTH)

RETURN
CALCULATE(
    AVERAGEX(
        VALUES('Calendar'[Year Month Number]),
        [Qty]),
    Period)

Regards