Help with Filtering Using A Separate Table

[powerlight1]

Hi, I need help with filtering using a separate table and calculating an average based on filtered records.

I have a Sales table

Date, Sales

1/11/2024, 1

2/11/2024, 2

3/11/2024, 3

4/11/2024, 4

5/11/2024, 5

6/11/2024, 9

and I have a separate "slicer_date[date]" table which I used as my date slicer

Date

1/11/2024

2/11/2024

3/11/2024

4/11/2024

5/11/2024

6/11/2024

I have a Filter_measure:

Filter_measure = IF( MAX(SALES[DATE]) = ALLSELECTED(slicer_date[date]).

When I select the first 5 days in my date slicer:

I just want to calculate the average of the first 5 days of Sales, ie from 01/11/2025 to 05/11/2024 which should be "3".  But when I put the "Sales" field into a table and change summarization to Average it comes up as "4" because it is averaging across 6 days and not just 5 days.

If I only have Average Sales then it is calculating incorrectly:

but when I include the field "date" it calculates correctly but I do not want to show "date" in the output table:

Do you know why the 5 days set in the date slicer is not passing into the table when I don't include "date" in the output?

[Ashish_Mathur]

Hi,

As long as the reaulationship between dates is properly set up (Many to One and Single) and the slicer is from the date table, this simple measure should work

Avg = average(Data[Sales])

Hope this helps.

[powerlight1]

I must explain, I can't set up a relationship between my Sales table and my slicer_date table because I have a second slicer_date2 table allowing the user the flexibility to select 2 different sets of dates from 2 different slider_date tables and returning both date period calcuation.

[powerlight1]

Thanks @Shahid12523 

That worked.  I'm new to PBI so was wondering why didn't the Filter Measure I created and placed in the visual's filter did the job? 

[danextian]

Hi @powerlight1 

What does the measure do? It has an incorrect syntax.

That aside and even if you use an implicit measure and assuming that there's only one row for each date, the average would still have returned correctly even without the date colum  had there been a relationship betweeen your sales and slicer tables.

If there are more than one rows for each date, you will need to create a measure to calculate the daily average

AVERAGEX ( ALLSELECTED ( slicer_date ), CALCULATE ( SUM ( sales[amount] ) ) )

[powerlight1]

@danextian 

the filter measure should be:

Filter_measure = IF( MAX(SALES[DATE]) = ALLSELECTED(slicer_date[date]),1,0).

[Shahid12523 · Accepted solution]

Your slicer table isn’t filtering Sales, so the average uses all 6 days.

Fix: Create a measure with TREATAS

Avg Sales by Slicer =
CALCULATE(
AVERAGE(Sales[Sales]),
TREATAS(VALUES(slicer_date[Date]), Sales[Date])
)

Now the slicer dates will filter Sales, and the average for 1–5 Nov will return 3.

Alternative: just relate slicer_date[Date] → Sales[Date] directly.