topic Re: Rolling 6 Month Average for data which has missing dates in DAX Commands and Tips

Rolling 6 Month Average for data which has missing dates

afaro — Thu, 20 Jun 2024 22:57:25 GMT

Before I begin, I want to not use AVERAGEX or DATESINPERIOD in my solution. I would prefer to only use basic functions like CALCULATE, MIN, MAX, FILTER, ALL, etc. I have described my attempt at the end.

I have data like this in a table called Sales

Date	Revenue
2 Jan 2021	1
12 Jan 2021	2
3 Feb 2022	3
7 Feb 2022	4
9 Mar 2022	1
6 April 2022	2
20 May 2022	3
12 June 2022	4
18 June 2022	1
12 July 2022	2
31 July 2022	3
8 August 2022	4

My Calendar table has daily granularity and I created a bridge Year-Month table to connect my calendar table with the above Sales table.

So I have Month-Year from Calendar table as rows and I tried writing this as value for each Month-Year like this:
Rolling 6 Month Average =
var maxCurMonthDate = EOMONTH(Calendar[Date],0)
var prev6Date = EOMONTH(maxCurMonthDate, -6)
var rollingRevenue = CALCULATE(SUM(Sales[Revenue]), FILTER(ALL(Calendar), Calendar[Date] <= maxCurMonthDate && Calendar[Date] > prev6Date))

return DIVIDE(rollingRevenue, 6)

This isn't working for some reason and I am looking for insights into why this isn't working.

Re: Rolling 6 Month Average for data which has missing dates

Anonymous — Fri, 21 Jun 2024 05:51:23 GMT

Hi afaro ,

You should ensure your model and relationships are correctly. Could you share your data model and relationships that we can better get and find out your problem.

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

Re: Rolling 6 Month Average for data which has missing dates

mark_endicott — Fri, 21 Jun 2024 09:25:24 GMT

afaro - My immediate assumption would be that it's something to do with your relationships or the fields that you have in the chart.

It's not clear why you have created the Month Year bridge to connect the fact to the calendar when you have dates in both - my first suggestion would be to remove the bridge and join on dates.

I would also check that you are using the date from the calendar in your visual - rather than the date from sales.

I know you said you don't want to use these, but I have always calculated a moving AVG by using the below - it's very efficient and optimal.

VAR NumOfMonths = 6 VAR LastCurrentDate = MAX ( 'Date_Table'[Date] ) VAR Period = DATESINPERIOD ( 'Calendar'[Date], LastCurrentDate, - NumOfMonths, MONTH ) VAR Result = CALCULATE ( AVERAGEX ( VALUES ( 'Calendar'[Month Year] ), [Revenue] ), Period ) VAR FirstDateInPeriod = MINX ( Period, 'Calendar'[Date] ) VAR LastDateWithSales = MAX ( 'Sales'[Date] ) RETURN IF ( FirstDateInPeriod <= LastDateWithSales, Result )

I hope this helps, if testing this works, please accept it as the solution.

Re: Rolling 6 Month Average for data which has missing dates

afaro — Fri, 21 Jun 2024 13:52:06 GMT

Because even though my Sales table has dates, it doesn't have every single date. In a given month there are not more than 10 dates for that month. Hence, the last date of that month is usually not there. That is why I created a year-month bridge table.