How to Filter Using a Separate Table

[powerlight1]

Hi Community,

I am doing some complex calculation and "OR Condition" filtering and need some help.

I have a simple table called Baseline with date and sales:

Date	Baseline Sales
1/11/2024	16.8658
2/11/2024	3.514709
3/11/2024	95.39742
4/11/2024	53.36697
5/11/2024	68.06059
6/11/2024	66.81327
7/11/2024	71.91321
8/11/2024	85.36628
9/11/2024	1.358787
10/11/2024	34.57078

and I have a second table called Actual sales:

Date	Actual Sales
1/11/2024	92.65061
2/11/2024	87.16787
3/11/2024	77.94617
4/11/2024	65.8121
5/11/2024	54.01474
6/11/2024	96.31909
7/11/2024	40.95989
8/11/2024	15.41474
9/11/2024	42.09341
10/11/2024	23.39248
11/11/2024	29.6792
12/11/2024	50.02887
13/11/2024	61.36338
14/11/2024	48.28854
15/11/2024	18.21006

The relationship between the two tables is just the "Date" column

In addition I've also created 2 independent date table to use as date slicers :

slicer_dates_BL =

SELECTCOLUMNS(

    Baseline

   ,"Date"

    ,[Date])

slicer_dates_ACT =

SELECTCOLUMNS(

    Actual

   ,"Date"

    ,[Date])

I then created a Measure to use as my filter:

Filter_Date =

IF(

    MAX(Baseline[Date]) IN ALLSELECTED(slicer_dates_BL[Date])  

    ,1

    ,0

) 

What I then want to show in a table the following:

1. The average sales from the first 5 days from the Baseline table for period 01.11.2024 to 05.11.2024
2. The average sales from the Actual table for period 11.11.2024 to 15.11.2024

For objective 1. above, I created the 2 Measure:

AVG_Baseline_Sales =

CALCULATE(

    AVERAGE(Baseline[Baseline Sales])

)

AVG_Baseline_Sales2 =

CALCULATE(

    AVERAGE(Baseline[Baseline Sales])

    ,FILTER(

        Baseline

        ,MAX([Date]) in ALLSELECTED(slicer_dates_BL[Date])

    )

)

I then place a table on my canvas placed Filter_Date  in the Filter section of the table and set to equal 1. 

I then set my baseline date slicer to the period 01.11.2024 to 05.11.2024

I then placed the both measures AVG_Baseline_Sales and AVG_Baseline_Sales2  into the table. 

You can see from the screen shot below AVG_Baseline_Sales is calculate the average for the entire 10days and not just the first 5 days, whereas AVG_Baseline_Sales2 is giving me blank.  How do I just show the average of the first 5 days sales without having to place the field "Date" into my results table?

[Ashish_Mathur]

Hi,

Based on the 2 tables that you have shared, show the expected result very clearly.

[v-dineshya]

Hi @powerlight1 ,

Thank you for reaching out to the Microsoft Community Forum.

Please follow below steps.

1. Created Actual and Baseline tables based on your input data.

2. Created disconnected tables  Slicer_dates_BL and Slicer_dates_ACT for slicers.

3. Created measures Avg_Baseline_Sales and Avg_Actual_Sales.

4. In Table visual, I have dragged measues and in slicers, i have choosed '1/11/2024' to '5/11/2024' in Slicer_dates_BL and '11/11/2024' to '15/11/2025' in Slicer_dates_ACT .

Please refer below output snap and attached PBIX file.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[v-dineshya]

Hi @powerlight1 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[v-dineshya]

Hi @powerlight1 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[v-dineshya]

Hi @powerlight1 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[MFelix · Accepted solution]

Hi  , 

For this I believe you do not need to have two calendar tables.

What I modeled was the following:

• Created a calendar table
  • Needs to start at 1st of January of the minimum year and end at 31 of December of maximum year
  • Be carefull to check the dates between both the sales and the baseline
  • In this case the calendar goes from January 1st 2024 to  31st December 2024
  • Create a one to many between the tables:
    • Calendar - Baseline
    • Calendar - Actual
    • Create the measures below:
      • AVG_Baseline_Sales = CALCULATE( AVERAGE(Baseline[Baseline Sales]), ALL('Calendar'[Date] )) AVG_Baseline_Sales2 = CALCULATE( AVERAGE(Baseline[Baseline Sales]) )Use the calendar table in your slicer
      • Result below

        The relationship will pick up the baselines corretly now you can also do a average of the sales and add the date to the visualization:

        If you want to keep the average for the 5 days you can create a measure similar to this one:AVG_Baseline_Sales 3 = CALCULATE( AVERAGE(Baseline[Baseline Sales]), ALLSELECTED('Calendar'[Date] )) 

        Please see attach file.