cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LsasS Frequent Visitor
Frequent Visitor

Re: Moving Average

Dear @MattAllington I'm sorry to disapoint You, but You don't need it anymore:

"When you create a model in Power Pivot or Analysis Services Tabular, you can apply the setting “Mark as Date Table” choosing a column of Date data type as the date in the table."

https://www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/

 

I'll be grateful if You'll shortly give a prove that i'm not right.

Sincerely Yours, Max

Super User
Super User

Re: Moving Average

The 2 concepts "you must have a calendar table to use inbuilt time intelligence" and "you don't have a mark as date table feature in Power BI" are completely independent. You do need a calendar table if you want to use inbuilt time intelligence. The reference at SQLBI is related to the use of surrogate keys between a date table and a data table. In Excel you can use surrogate keys and use the "mark as date table feature". In Power BI you must use a date column. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
zixindh Visitor
Visitor

Re: Moving Average

Is it possible to remove the first two months? @v-sihou-msft 1/1/2016 and 2/1/2016 average 100 and 125 are not the numbers I want to include in my report.

Anonymous
Not applicable

Re: Moving Average

Trailing_Average_3_Months = CALCULATE(AVERAGEX('Active List Table', 'Active List Table'[Sales_Pric]),DATESINPERIOD('Active List Table'[List_Date],LASTDATE('Active List Table'[List_Date]), -3, MONTH))

 

In my case this is not work. 

Preetish_1 Regular Visitor
Regular Visitor

Re: Moving Average - Please Help!

Hi @arunksri, @Anonymous,

 

I use a similar formula and am not able to get the moving average.

Were you guys able to figure out what the problem was?

Maybe I am having the same problem as you guys.

 

Thanks,
Preetish

Anonymous
Not applicable

Re: Moving Average - Please Help!

via below logic, you can find the Trailing average of the 12 months. 

 

 

[Trailing 12 Month Average ] = DIVIDE(
CALCULATE(
SUM(Avg_sales[Sales_Count]),
DATESBETWEEN(
Avg_sales[List_Date],
FIRSTDATE(DATEADD(Avg_sales[List_Date],-12,MONTH)),
LASTDATE('Avg_sales'[List_Date])
)
),12)

 

 

Preetish_1 Regular Visitor
Regular Visitor

Re: Moving Average - Please Help!

Thank you @Anonymous . I just got it working with a similar DAX expression.  

traceyvanp Visitor
Visitor

Re: Moving Average - Please Help!

I have the same problem:

Table with one column as Survey with values 0, 1, -1

Another column as Date, in date format "m/d/Y".

 

The 3 month moving average is the same as the average. I've tried multiple things but still stumped. Woman Sad help?

Max01 Regular Visitor
Regular Visitor

Re: Moving Average - Please Help!

Hello,

 

I followed the steps above to create the moving average however I get an error stating that there are duplicates in my DATESINPERIOD column (this is because there are and there are supposed to be...). I was hoping it would add all the values for one day together and then average these - do you have any suggestions?

 

Thanks Smiley Happy

 

Max

 

 

salberts Frequent Visitor
Frequent Visitor

Re: Moving Average - Please Help!

In my application this approach collected an extra month's worth of data. The corrected forumula which worked for me is below. I tested this with manual calculations in Excel.

 

[Trailing 12 Month Average ] = DIVIDE(
CALCULATE(
SUM(Avg_sales[Sales_Count]),
DATESBETWEEN(
Avg_sales[List_Date],
FIRSTDATE(DATEADD(Avg_sales[List_Date],-11,MONTH)),
LASTDATE('Avg_sales'[List_Date])
)
),12)