4 Week Average with a Day Filter

[justinremy]

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 had a look at the previous forums however, because my data requires to be summed then averaged I am having issues. Apologies for the screenshots in advance.

[Jihwan_Kim]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

WINDOW function (DAX) - DAX | Microsoft Learn

Sales measure: = 
SUM( Sales[Sales] )

Expected result measure: =
VAR _currentdayname =
    MAX ( 'Calendar'[Day name sort] )
VAR _currentdate =
    MAX ( 'Calendar'[Date] )
VAR _recentfoursamedaytable =
    WINDOW (
        1,
        ABS,
        4,
        ABS,
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Date] <= _currentdate
                && 'Calendar'[Day name sort] = _currentdayname
        ),
        ORDERBY ( 'Calendar'[Date], ASC )
    )
VAR _condition =
    COUNTROWS ( _recentfoursamedaytable ) = 4
RETURN
    IF ( _condition, AVERAGEX ( _recentfoursamedaytable, [Sales measure:] ) )

[justinremy]

Hi Jihwan_Kim, really appreciate it for responding. I am still having issues given that I am having to sum the patron hours for the day before averaging it, as the data I have is based on an hourly/location basis.

[Jihwan_Kim]

Hi,

Please provide a sample pbix file with how the expected outcome looks like.

Thanks. 

[justinremy]

Hi Jihwan_Kim 

I've attached it here

https://drive.google.com/file/d/1fC-Z7UIfen1OZiedW1kMzYUuU8K_XZx4/view?usp=drivesdk

thanks again! 

[Jihwan_Kim · Accepted solution]

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] ) )
        )
    )

[justinremy]

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]

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