Forum Discussion

Sirona_Kis's avatar
Sirona_Kis
New Member
2 years ago
Solved

Rolling 6 month Average

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

 

 

 

Need Help
Tutorial Requests
  • sergej_og's avatar
    sergej_og
    2 years ago

    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

3 Replies

  • sergej_og's avatar
    sergej_og
    Icon for Super User rankSuper User
    2 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi sergej_og 

     

    I am trying to get average of last 3 months for Status% using this code but its not giving correct average. I am using measure and Status%  is also measure

     

    Rolling 3M Avg =
    VAR Period =
        DATESINPERIOD(
            'TestBoard'[Exec Date],
            MAX('TestBoard'[Exec Date]),
            -3,
            MONTH)

    RETURN
    CALCULATE(
        AVERAGEX(
            VALUES('TestBoard'[Exec Date]),
            [Status%]),
        Period)
     
     

    Can you please advise whats going wrong here.. Thanks in advance!!

    • sergej_og's avatar
      sergej_og
      Icon for Super User rankSuper User
      2 years ago

      Hey Anonymous,
      what exactly do you expect as result of your measure "Rolling 3M Avg"?
      (32.38% + 24.94% + 17.23%) / 3 =  24.85% (this result for the row 2023 - January)?

      Take attention.
      The formula is using this field (granularity level)

       

      Your formula is based on "Date" field.



      Regards