Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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 )
)
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!
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/
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.
@danno thank you but I have amended the measure to get the results needed without having to add too many steps in.
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 )
)
@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 )
)
User | Count |
---|---|
88 | |
73 | |
69 | |
64 | |
56 |
User | Count |
---|---|
98 | |
92 | |
84 | |
74 | |
66 |