cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## Rolling 6 Month Average for data which has missing dates

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.

3 REPLIES 3
Super User

@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.

Helper III

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.

Anonymous
Not applicable

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.

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

Best Regards,

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors