How do I select which tables to calculate on based on the date?

[Superklez]

I have 4 tables, say, data_2020, data_2021, data_2022, and calendar, which are all connected through DirectQuery (because each dataset is too large), except for the calendar table, which is just a lookup table. Now, for example, I want to calculate the total profits. So, I have a DAX measure that is defined as

Total Profit =
SUM('data_2022'[total_profit])
+ SUM('data_2021'[total_profit])
+ SUM('data_2020'[total_profit])

But I was told that this would still genearate queries for the data_2021 and data_2020 tables even if 'calendar'[date] is filtered to show only data for 2022. I have (I think) a solution that is more optimized and it is defined as

Total Profit =
VAR last_date_2021 = CALCULATE(LASTDATE('data_2021'[date]), ALL('data_2021'[date]))
VAR last_date_2020 = CALCULATE(LASTDATE('data_2020'[date]), ALL('data_2020'[date]))
RETURN
SUM('data_2022'[total_profit])
+ IF(MIN('calendar'[date]) < last_date_2021,
  SUM('data_2021'[total_profit]))
+ IF(MIN('calendar'[date]) < last_date_2020,
  SUM('data_2020'[total_profit]))

But this only works best if I filter the minimum date in 'calendar'[date]; I don't think it would optimize the cases wherein, for example, I set the max date to be 2021 or earlier. How do I improve on this? Any help would be much appreaciated. Thank you in advance.

[AlexisOlson]

If at all possible, I'd strongly recommend finding a way to combine (append) your data tables rather than querying them separately and having to combine them in your measures.

[johnt75]

The first thing to do would be to confirm whether or not queries are being passed to the 2020 & 2021 tables if you have filtered only for 2022.

The easiest way to do this would be to add a card visual which includes the measure and  which is filtered to only 2022. Use Performance Analyzer to grab the query generated and paste it into DAX Studio. In there you can enable Server Timings and then execute the query, which will allow you to look at the queries sent to the Direct Query datasets. If you don't see any mention of the 2020 or 2021 tables then you don't need to worry about it

[Superklez]

Thanks for your advise, I'll definitely give this a try when I can. Right now, I think the best solution would be to add a slicer where the user can choose which years to display, and maybe my DAX measure would be defined as

Total Profit =
VAR years_selected = DISTINCT('calendar'[year])
RETURN
0
+ IF (2022 IN years_selected,
      SUM('data_2022'[total_profit]))
+ IF (2021 IN years_selected,
      SUM('data_2021'[total_profit]))
+ IF (2020 IN years_selected,
      SUM('data_2020'[total_profit]))

 What do you think? (I haven't tested it yet, I'm just trying to think of a solution.)

[johnt75]

I think that should do it.