calculate month average of daily values in a matrix with dates in the row filter

[PowerBeeTree]

I have the two tables below, one sales_table with the daily sales and another one check_dates_table which has one date columns which is a subset of the dates found in sales_table. 

sales_table:

date_sales     sales

03/01/2024	10
03/02/2024	20
03/03/2024	22
03/04/2024	5
03/05/2024	7
03/06/2024	15
03/07/2024	34
03/08/2024	56
03/09/2024	2
03/10/2024	15
03/11/2024	5
03/12/2024	7
03/13/2024	25
03/14/2024	32
03/15/2024	45
03/16/2024	23
03/17/2024	2
03/18/2024	3
03/19/2024	24
03/20/2024	15
03/21/2024	7

check_dates_table:

date_check

03/01/2024

03/05/2024

03/06/2024

03/07/2024

03/11/2024

03/12/2024

03/14/2024

The relationship between the two is the following:

for the end output, i want to get a matrix with the check dates in the rows and the sales, average of sales in the check_dates in the values. Something like this in other words (reproduced in excel):

i have defined a measure in the sales_table that calculates the average sales:

msr_AVG = AVERAGE(sales_table[sales])

however when i add id to the matrix values i cant get it to ignore the rowfilter of the "date_check".

Looks that i have to somehow create a measure that ignores the row filter but everything i tried didnt work. Anyone can please help?

[PowerBeeTree]

thanks @Greg_Deckler 

is it possible to do something like the following?

Measure Avg = 
  VAR __Date = MAX( 'check_dates_table'[date_check] )
  VAR __Year = YEAR( __Date )
  VAR __Month = MONTH( __Date )
  VAR __Table = FILTER( ALL( 'sales_table'), sales_table[date_sales] IN check_dates_table[date_check])
  VAR __Result = AVERAGEX( __Table, [sales] )
RETURN
  __Result

However it looks like when i add the "sales_table[date_sales] IN check_dates_table[date_check]" filter then the "date_sales" is added back to the context filter despite having the ALL. 

So in theory, i believe i want something that:

1. first filters down the sales_table to the set of rows i want to calculate the average on and
2. afterwards, on that result remove the row context so that the measure isn't affected by the rows of the matrix.

Unless my logic is completely wrong and need a tutorial (my 1st month on DAX/ PBI).

The example provided is just for illustration purposes and is a coincidence that all the check dates occur in one single month.

It seems that i need to make the measure ignore the row context of the provided check_dates ( if it's possible at at). 

[Greg_Deckler]

@PowerBeeTree That way won't work most likely. The reason is that in your table visual, you have the rows by date. Thus, in your IN check_dates_table[date_check] the only date in there is going to be the date for that row which is not what you want. That is why you can't do it that way.

[PowerBeeTree]

thanks @Greg_Deckler 

yes i have figured that keeping them in there is causing the issue however i need to see the "active" date.

Any workarounds? It sounds that my problem is similar to calculating MTD however when i add the TOTALMTD measure (visual measure) i am getting the rolling average instead of the overall average.

[EDIT]

It looks like i am able to achieve what i want through visual calculation.

Is it possible to create a measure that does just that or is it possible only via a visual calculation?

[Greg_Deckler]

@PowerBeeTree OK, I mocked this up and now better understand the issue. Here is a revised measure and PBIX is attached below signature:

Measure Avg = 
  VAR __Date = MAX( 'check_dates_table'[date_check] )
  VAR __CheckDates = DISTINCT(check_dates_table[date_check])
  VAR __Year = YEAR( __Date )
  VAR __Month = MONTH( __Date )
  VAR __Table = FILTER( ALL( 'sales_table'), YEAR( [date_sales] ) = __Year && MONTH( [date_sales] ) = __Month && [date_sales] IN __CheckDates)
  VAR __Result = AVERAGEX( __Table, [sales] )
RETURN
  __Result

[PowerBeeTree]

@Greg_Deckler maybe i miscommunicated my challenge.

I opened the PBI file (thanks for it!)

instead of the matrix presented in it i want to have the following in the "measure Avg" column:

[Greg_Deckler · Accepted solution]

@PowerBeeTree My bad, it's a very small change:

Measure Avg = 
  VAR __Date = MAX( 'check_dates_table'[date_check] )
  VAR __CheckDates = ALL(check_dates_table[date_check])
  VAR __Year = YEAR( __Date )
  VAR __Month = MONTH( __Date )
  VAR __Table = FILTER( ALL( 'sales_table'), YEAR( [date_sales] ) = __Year && MONTH( [date_sales] ) = __Month && [date_sales] IN __CheckDates)
  VAR __Result = AVERAGEX( __Table, [sales] )
RETURN
  __Result

[PowerBeeTree]

this is great @Greg_Deckler !

Please correct me if i'm wrong but it seems that i needed to break out of the filter context applied by " check_dates_table[date_check] ". 

Using DISTINCT keeps the fitler context in place so i have to use ALL to escape from it. Right?

Is there by any chance a list/cheatsheet of the functions that maintain in place/ escapre from the filter context?

[Greg_Deckler]

@PowerBeeTree Correct, ALL escapes the filter context. ALL is the main function for doing this. There is also ALLSELECTED which is supposed to just escape the internal filters but not the external filters (like slicers for example). There is also ALLEXCEPT which removes all filters except those specified. 

There are also special functions that only really work with CALCULATE and CALCULATETABLE like REMOVEFILTERS. I don't generally use CALCULATE so I generally don't care about these.

Here is the link to the DAX function reference: DAX function reference - DAX | Microsoft Learn

Expand the Filters section and you'll find all the different functions that can affect filter context, etc.

Also, note that I am relying on an odd quirk of the ALL function where if you use the column form it returns distinct values.

[Greg_Deckler]

@PowerBeeTree The first variable grabs the active date. What is the issue with the measure? If you need to preserve external filters (external to the visualization) you can use ALLSELECTED vs. ALL.

[Greg_Deckler]

@PowerBeeTree Maybe:

Measure Avg = 
  VAR __Date = MAX( 'check_dates_table'[date_check] )
  VAR __Year = YEAR( __Date )
  VAR __Month = MONTH( __Date )
  VAR __Table = FILTER( ALL( 'sales_table'), YEAR( [date_sales] ) = __Year && MONTH( [date_sales] ) = __Month )
  VAR __Result = AVERAGEX( __Table, [sales] )
RETURN
  __Result