Forum Discussion

afaro's avatar
afaro
Icon for Helper III rankHelper III
2 years ago

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

DateRevenue
2 Jan 20211
12 Jan 20212
3 Feb 20223
7 Feb 20224
9 Mar 20221
6 April 20222
20 May 20223
12 June 20224
18 June 20221
12 July 20222
31 July 20223
8 August 20224

 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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

  • mark_endicott's avatar
    mark_endicott
    Icon for Super User rankSuper User
    2 years ago

    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. 

    • afaro's avatar
      afaro
      Icon for Helper III rankHelper III
      2 years ago

      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.