topic Re: 4 Week Average with a Day Filter in DAX Commands and Tips

4 Week Average with a Day Filter

Anonymous — Tue, 06 Feb 2024 05:19:32 GMT

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.

Re: 4 Week Average with a Day Filter

Jihwan_Kim — Tue, 06 Feb 2024 06:01:17 GMT

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

Re: 4 Week Average with a Day Filter

Anonymous — Tue, 06 Feb 2024 06:06:39 GMT

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.

Re: 4 Week Average with a Day Filter

Jihwan_Kim — Tue, 06 Feb 2024 06:16:51 GMT

Hi,

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

Thanks.

Re: 4 Week Average with a Day Filter

Anonymous — Tue, 06 Feb 2024 22:34:48 GMT

Hi Jihwan_Kim

I've attached it here

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

thanks again!

Re: 4 Week Average with a Day Filter

Jihwan_Kim — Wed, 07 Feb 2024 04:34:47 GMT

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

Re: 4 Week Average with a Day Filter

Anonymous — Wed, 21 Feb 2024 01:05:06 GMT

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?

Re: 4 Week Average with a Day Filter

Jihwan_Kim — Wed, 21 Feb 2024 03:59:34 GMT

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