Forum Discussion
moving average help
I'm trying to add a Moving 3 month average to my Line Chart. The field being used for the x-axis is a text field so I can just see Month-Year. I put the Date that the text came from in the calculation the moving average line is the same as my data line.
How can I make the x-axis field (Year Month Incident Resolved) a date field but not mess with the formatting of the line chart? If I use the text field in my moving average, it obviously throws an error saying its not a date field.
Hi gilmore_staci ,
Thank you for sharing the formula. The error with AVERAGE occurs because Dealer Resolution Rate is a measure, not a column. AVERAGE works only with columns, so Power BI doesn’t allow it here. This is also why AVERAGEX doesn’t return an error but gives zeros, as it iterates row by row while your percentage is already calculated as a measure.
Since the value is already aggregated, there’s no need to average it again over the Case table. Instead, the moving average should evaluate the measure over the last 3 months using the date context, allowing Power BI to calculate the measure across the previous 3 months based on the date filter, rather than averaging row-level values, which can lead to zeros.
Hope this helps....
13 Replies
hi gilmore_staci ,
Time intelligence functions (like DATESINPERIOD, LASTDATE ) rely on dedicated date table.
Supposing you have a data table like below:date rate 2025/1/1 0.1 2025/2/1 0.12 2025/3/1 0.14 2025/4/1 0.16 2025/5/1 0.18 2025/6/1 0.2 2025/7/1 0.22 2025/8/1 0.24 2025/9/1 0.26 2025/10/1 0.28 2025/11/1 0.3
You may try the following:
1) write a calculated table like:dates = ADDCOLUMNS( CALENDAR(MIN(data[date]), MAX(data[date])), "YY/MM", FORMAT([Date], "YY/MM") )2) relate data[date] with the dates[date]
3) plot an chart/table visual with dates[yy/mm] and a measure like:
3M Avg Moving = VAR _date = MAX(dates[date]) VAR _result = CALCULATE( AVERAGE(data[rate]), DATESINPERIOD(dates[Date], _date, -3, MONTH) ) RETURN _resultit works like:
Or try without Time Intelligence Functions, like:
1) write a calculated column in data table like:YY/MM = FORMAT([date], "YY/MM")2) plot a visual with data[yy/mm] with a measure like:
3M Avg Moving NonTIF = VAR _end = MAX(data[date]) VAR _start = EDATE(_end, -3) VAR _result = CALCULATE( AVERAGE(data[rate]), data[date]>_start && data[date]<=_end, ALLEXCEPT(data, data[date]) ) RETURN _resultit works like:
gilmore_staci
In order to use any time intelligence calculation (as DATESINPERIOD), you need a well-formed date table.
I understand that the "case" table is the table where you register your transactions (and therefore should not be your date table).
please try to adjust your model creating a calendar table and its relationship with the case table. please check if it resolve your needs.
https://learn.microsoft.com/en-us/dax/datesinperiod-function-dax
If this response was helpful in any way, I’d gladly accept a kudo.
Please mark it as the correct solution. It helps other community members find their way faster.I think I'm missing something. In my visual I have
x-axis:
Year Date Incident Resolved = ('Case'[Date Incident Resolved].[Month]) & "-" & ('Case'[Date Incident Resolved].[Year])Which is text'Case'[Date Incident Resolved] is modeled to my 'Calendar'[Date] fieldDate Incident Resolved is a Date fieldy-axis:Dealer Resolution Rate = IFERROR('Case'[FalseCount]/'Case'[Count of incidents resolved in Month], 0)which is percentageCount of incidents resolved in Month = CALCULATE(COUNTROWS('CASE'),USERELATIONSHIP('Case'[Incident Resolved],'Calendar'[Date]))Incident Resolved is a Date/Time fieldDo I create YY/MM in my Calendar table or Case table? I've added it to both and added in your 3M Avg calcs (updating to my info) to both and I'm getting no data in my visual. Do my other calculations need to be reworked? If I add in YY/MM to the x-axis i'm getting all the data lines again for Dealer Resolution Rate, not by month.gilmore_staci if you want to use time intelligence functions: in the report you should use the "date" from a column of the Dimensional DateTable not from a column of the "Case" table.
You should have a DateTable to be used as a dimensional table: with a relationship one to many pointing the dates column in your transactional table.
Please see how what FreemanZ includes diferent tables in his first post: creating a DateTable; and proposed (in his second post) an option using the dates from your transactional table.
you dont need a measure to get the format of MM/YY to be used in the report, this calculated value won´t have a valid field with that calculated value in the table to iterate over that field...
please check FreemanZ second post.
Create a new CalculatedColumn inside your case table.
Use that CalculatedColumn (directly) in your report.
Use the next measure in your report:MovingAvg_3M_CaseDates = VAR _end = MAX('Case'[Date Incident Resolved]) VAR _start = EDATE(_end, -3) VAR _result = CALCULATE( AVERAGE('Case'[Dealer Resolution Rate]), 'Case'[Date Incident Resolved]>_start && 'Case'[Date Incident Resolved]<=_end, ALLEXCEPT('Case', 'Case'[Date Incident Resolved]) ) RETURN _resultIf this response was helpful in any way, I’d gladly accept a kudo.
Please mark it as the correct solution. It helps other community members find their way faster.
why not using a visual calc here? But in order to help you can you show pictures of the "mess" you refer to?
If this helped, please consider giving kudos and mark as a solution
@me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
Hi gilmore_staci ,
FreemanZ & FBergamaschi , response looks good. Please let us know if you are facing any issues or if you need any additional details or assistance from our side.
Thank you for your valuable response, FBergamaschi FreemanZ .
Regards,
Yugandhar.
HI gilmore_staci ,
We haven’t received a response from your end yet. Please let us know whether the issue has been resolved or if you’re still facing any difficulties. Feel free to reach out if you need further assistance.
Thank you.
I understand what needs to be done, but in order to get my original data, the x-axis date is coming from my Case table. If I swtich and model the date to the Date in my Calendar table, my original data all ends up as either zero's or I end up getting every single day, not a combined month data. I will post pics.
Hi gilmore_staci ,
Based on the screenshots, it seems the relationship between your Case date and Calendar date is inactive. This causes the visual to display 0s when you set the X-axis to the Calendar table, as it doesn’t filter the Case table correctly.
Try these steps.
1. Verify that the relationship between Case[Date Incident Resolved] and Calendar[Date] is active.
2. Use a Month Year column from the Calendar table for the X-axis instead of a text field from the Case table.
3. Note that using the full Date field will show daily values in Power BI, so a Month-Year column will keep it grouped by month.
Once the relationship is active and the axis is set from the Calendar table, your monthly values and 3 month moving average should display correctly.
Thanks for sharing more details and your insights.
V-yubandi-msft thank you, I'm getting closer. The moving average is giving zero for the data points but the line seems correct. The only thing I can see is the AVERAGE in the calculation. If I use AVERAGE it says I can't use Dealer Resolution Rate its either not in the table or can't be used in the expression. It doesn't give an error if I use AVERAGEX, but also gives me zeros
Dealer Resolution Rate(percentage) =
IFERROR('Case'[FalseCount]/'Case'[Count of incidents resolved in Month], 0)Moving_Average_3_Months =VAR _end = MAX('Case'[Date Incident Resolved])VAR _start = EDATE(_end, -3)VAR _result =CALCULATE(AVERAGEX('Case', 'Case'[Dealer Resolution Rate]),'Case'[Date Incident Resolved]>_start && 'Case'[Date Incident Resolved]<=_end,ALLEXCEPT('Case', 'Case'[Date Incident Resolved]))RETURN _result
Hi gilmore_staci ,
Could you please let us know whether your issue has been resolved, or if you are still facing any issues. If you need any additional details or clarification, please feel free to let us know.
