Rolling 3 month average on a measure (going back 3 months for each data point)

[rush]

Hi All

I need help with creating a measure that will average the measure below going back 3 months for each data point as I would like to use both measures in a trend line.

Billing Rate Avg. 2 = 

VAR Total_Rev = SUM(  Billing_Summary[Revenue] )

VAR Total_Bill_Hours = SUM(  Billing_Summary[Billable Hours] )

RETURN 

DIVIDE( Total_Rev , Total_Bill_Hours , 0 )

e.g. 

In May 2019 the trendline point (for May 2019) would be the average of the average rate for Mar, Apr & May 2019

In June 2019 the trendline point (for June 2019) would be the average of the average rate for Apr, May & June 2019

In July 2019 the trendline point (for Jul 2019) would be the average of the average rate for May, June & Jul 2019

Data sample 

[Anonymous]

I need to calculate a 3 average revenue with the start of the financial year being the start of the calculation period. The financial year is 1 July - 30 June. I want to be able to get the "3-month avaerge" section to calculate in PowerBI but I dont know how to do it using the start of the financial year as the starting point.  

	Actual results	3-month average
	Jul-20	Aug-20	Sep-20	Oct-20	Jul-20	Aug-20	Sep-20	Oct-20
Fee revenue 1	$                             10,000.00	$  34,000.00	$       45,908.00	$           59,876.00	$       10,000.00	$  34,000.00	$  29,969.33	$  46,594.67

Any helpwould be greatly appreciated

Thank you!

[amitchandak]

@rush 

Try this with a date calendar

Rolling 3 = CALCULATE(average(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-3,MONTH)) 
Rolling 3 = CALCULATE(average(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-3,MONTH))  

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[danno]

Hi, 

there are a few things to do with the data before calculating a rolling average. 

firstly the model needs to know the sequence of months, so that when you create a rolling average, the dates are in the correct order.  If you don't already have a date table at a month level then create one, if not then you can derive it from you available data. Here is a powerquery to take your table and create a Calendar table from it: (Note: the source is a table created from pasting your sample data into powerbi, you can change this for an Excel or other data source) 
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XdDBCsIwDAbgV5GeFCakWdusR1E8CFPQ49hhjuFFdIgTfHu3pAXXUw4f/980VaXK5rVG0H6xRFipTJ2dhXE4zFWdVWrTJ0zAXFjmsvlG1pJGZkvMh+ERWHvhgtlFvsd0HtJ2YoPy9nCbl5PmNAlfuj5ZLZfVCuZT+56XE+A4SBvm4/OTlINjln/vujbZ3HO597J5M30MYUyjpPlqpOUs++4aOV6NmN029MvZ/wv4MmScqusf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Month & Year" = _t, #"Billing Rate Avg." = _t, #"3 month average (outcome)" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Month & Year", type text}, {"Billing Rate Avg.", type text}, {"3 month average (outcome)", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Billing Rate Avg.", "3 month average (outcome)"}),
#"Inserted Text Before Delimiter" = Table.AddColumn(#"Removed Columns", "Text Before Delimiter", each Text.BeforeDelimiter([#"Month & Year"], " "), type text),
#"Inserted Text Between Delimiters" = Table.AddColumn(#"Inserted Text Before Delimiter", "Text Between Delimiters", each Text.BetweenDelimiters([#"Month & Year"], "(", ")"), type text),
#"Renamed Columns" = Table.RenameColumns(#"Inserted Text Between Delimiters",{{"Text Before Delimiter", "Month Year"}}),
#"Inserted Text Before Delimiter1" = Table.AddColumn(#"Renamed Columns", "Month", each Text.BeforeDelimiter([#"Month & Year"], "-"), type text),
#"Inserted Month" = Table.AddColumn(#"Inserted Text Before Delimiter1", "Month Number", each Date.Month(Date.From([Month Year])), Int64.Type),
#"Inserted Year" = Table.AddColumn(#"Inserted Month", "Year", each Date.Year(Date.From([Month Year])), Int64.Type),
#"Added Custom Column" = Table.AddColumn(#"Inserted Year", "YearMonth", each Text.Combine({Date.ToText(Date.From([Month Year]), "yyyy"), Date.ToText(Date.From([Month Year]), "MM")}), type text)
in
#"Added Custom Column"

- most of this was created using add column from examples. 

2.  Now we have a calendar table we can create a function to do a rolling average.  this function will only return rows where there is 1 valid month, so we have to wrap the function with an if statement to control its behaviour.  We will use IsFiltered to check that we have a month that we can calculate a rolling average from. 

I've done it in steps to show how the DAX is constructed, but you can collapse some of these statements into a single expression. 

Rolling Average =

VAR CurrentMonth = SELECTEDVALUE('Calendar'[YearMonth])

VAR MonthFilter = FILTER(All('Calendar'), 'Calendar'[YearMonth] <= CurrentMonth)

VAR RankedMonths = ADDCOLUMNS(MonthFilter, "Rank", RANKX(MonthFilter, [YearMonth]))

VAR Top3Months = FILTER(RankedMonths, [Rank] <=3)

RETURN IF(ISFILTERED('Calendar'),

CALCULATE(AVERAGE('Table'[Billing Rate Avg.]), Top3Months)

)

[rush]

@danno thank you but I have amended the measure to get the results needed without having to add too many steps in.

[Mariusz · Accepted solution]

Hi @rush 

You can adjust the below measure to your needs

Sales Rolling 12 months = 
CALCULATE(
    [Sales],
    DATESINPERIOD( 'Calendar'[Date], MIN( 'Calendar'[Date] ) -1, -12, MONTH )
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[rush]

@Mariusz Thank you very much.

I have amended the measure to cater for the desired result.

Measure:

CALCULATE(
    [Billing Rate Avg. 2],
    DATESINPERIOD( 'Dim_Date'[Calendar_Date], MIN(  'Dim_Date'[Calendar_Date] )  , -3 , MONTH )
)