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

7 Replies

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

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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        2 years ago

        Hi,

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

        Thanks.