Forum Discussion

joshua1990's avatar
joshua1990
Post Prodigy
1 year ago
Solved

Exclude blank weeks from Rolling Average

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?

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    1 year ago

    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

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    1 year ago

    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's avatar
      v-aatheeque
      Community Support
      1 year ago

      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!