Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

4 Week Average with a Day Filter

Hi all, I am hoping to create a 4 Week average which can be changed based off a day slicer. For example if the Slicer is on a Monday then it would show average for the last 4 mondays etc. I have h...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

    Hi,

    Thank you for your message.

    Please check the below and the attached pbix file if it suits your requirement.

     

    4 Week Average = 
VAR _fourweekstable =
    WINDOW (
        -3,
        REL,
        0,
        REL,
        SUMMARIZE ( ALL ( 'PpOH Request from Harold' ), 'Calendar'[Week Ending Date] ),
        ORDERBY ( 'Calendar'[Week Ending Date], ASC )
    )
VAR _condition =
    COUNTROWS ( _fourweekstable ) = 4
RETURN
    IF (
        _condition,
        AVERAGEX (
            _fourweekstable,
            CALCULATE ( SUM ( 'PpOH Request from Harold'[PatronHours] ) )
        )
    )
Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
2 years ago

Hi,

Thank you for your message.

Please check the below and the attached pbix file if it suits your requirement.

 

4 Week Average = 
VAR _fourweekstable =
    WINDOW (
        -3,
        REL,
        0,
        REL,
        SUMMARIZE ( ALL ( 'PpOH Request from Harold' ), 'Calendar'[Week Ending Date] ),
        ORDERBY ( 'Calendar'[Week Ending Date], ASC )
    )
VAR _condition =
    COUNTROWS ( _fourweekstable ) = 4
RETURN
    IF (
        _condition,
        AVERAGEX (
            _fourweekstable,
            CALCULATE ( SUM ( 'PpOH Request from Harold'[PatronHours] ) )
        )
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Jihwan_Kim,

    Just wanted to say thank you as it worked perfectly. However because the data file is so big I have moved to a DirectQuery from Big Query. I am now getting this issue The column 'Week Ending Date1' specified in the 'SUMMARIZE' function was not found in the input table. What would a workaround be?

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

      Hi,

      In the case of having weak relationship (between direct query table and import table), summarize dax function does not work. Summarize dax function works well under strong relationship.

      Summarize is just one of many dax functions that describes the outcome as table.

      In this case, use other dax functions that construct tables.

       

      Table manipulation functions (DAX) - DAX | Microsoft Learn