Forum Discussion

joshua1990's avatar
joshua1990
Icon for Post Prodigy rankPost 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 = DATESINP...
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
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity 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
Icon for Community Support rankCommunity 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!