Calculate 3 months rolling

[Anonymous]

Looking for advice on how to calculate 3 months rolling average when data are 0 for some months.

I would like to calculate for Full Year but actual data should be used when available.

The first calculation should come in April and use Data for AVERAGE JAN, FEB, MAR

[Anonymous · Accepted solution]

Hi @Anonymous ,

I have created a simple sample, please refer to my pbix file to see if it helps you.

result =
VAR _1 =
    EDATE ( MAX ( 'Table'[date] ), -3 )
VAR _year =
    YEAR ( _1 )
VAR _month =
    MONTH ( _1 )
VAR _date =
    DATE ( _year, _month, 1 )
VAR _nowdate =
    MAX ( 'Table'[date] )
RETURN
    CALCULATE (
        AVERAGE ( 'Table'[value] ),
        FILTER ( ALL ( 'Table' ), 'Table'[date] >= _date && 'Table'[date] < _nowdate )
    )

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur · Accepted solution]

Hi,

Try this approach

1. Create a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name column by the Month number
2. Create a relationship (Many to One and Single) from the Date column of your Data Table to the Date column of the Calendar Table
3. To your visual, drag Year and Month name from the Calendar Table
4. Write these measures

Measure = sum(Data[Revenue])

Rolling average = averagex(SUMMARIZE(calculatetable(Calendar,datesbetween(calendar[date],minx(all(calendar),calendar[date]),max(calendar[date]))),calendar[year],calendar[month name],"ABCD",[Measure]),[ABCD])

Hope this helps.

[Anonymous · Accepted solution]

Hi @Anonymous ,

I have created a simple sample, please refer to my pbix file to see if it helps you.

result =
VAR _1 =
    EDATE ( MAX ( 'Table'[date] ), -3 )
VAR _year =
    YEAR ( _1 )
VAR _month =
    MONTH ( _1 )
VAR _date =
    DATE ( _year, _month, 1 )
VAR _nowdate =
    MAX ( 'Table'[date] )
RETURN
    CALCULATE (
        AVERAGE ( 'Table'[value] ),
        FILTER ( ALL ( 'Table' ), 'Table'[date] >= _date && 'Table'[date] < _nowdate )
    )

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Fowmy]

@Anonymous 

Can you share some sample data with the desired output to have a clear understanding of your question?
Mention whether you want a calculated column or measure.
You can either paste your data in the reply box or save it in OneDrive, Google Drive, or any other cloud-sharing platform and share the link here.