Exclude blank weeks from Rolling Average

[joshua1990]

hey experts!

I am using the Rolling AVG pattern from SQLBI and facing a minor issue now:

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

I am using a bar chart per week that shows me the actual sales. Not every week has a value since not every week we have sales.

Now using the rolling average I get values for the blank weeks - what makes sense.

But I would like to exclude these weeks. How?

[Greg_Deckler · Accepted solution]

@joshua1990 If you use Better Rolling Average this is quite simple:

Better Rolling Average = 
    VAR __EndDate = MAX('Table'[Date])
    VAR __MonthsAgo = EOMONTH(__EndDate, -6)
    VAR __StartDate = DATE(YEAR(__MonthsAgo), MONTH(__MonthsAgo), 1)
    VAR __Table = 
      FILTER(
        SUMMARIZE(
            FILTER(ALL('Table'),[Date]>=__StartDate && [Date]<=__EndDate),
            'Table'[Month],
            "__Value",[Sales])
        ),
        "__Value" <> BLANK()
      )
    VAR __Result = AVERAGEX(__Table,[__Value])
RETURN
    __Result

[v-aatheeque]

Hi @joshua1990 

Have you resolved the issue? If yes, kindly mark the helpful answer as a solution if you feel that makes sense. Welcome to share your own solution. More people will benefit from the thread.

Should you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum!