Hello,
I work at an airline and I'm trying to compare todays sales with comparison years sales. My problem is with filtering and also the fact that I'm working with different years. Let me illustrate I have a couple of tables. First one is the shifted date table since it is very import to compare day on day sales. So you compare 18 sep 2021 with 19 sep 2022 since both were a monday:
| Compyear | SalesDate | DATESHIFT |
| 2018 | 6.9.2022 | 4.9.2018 |
| 2018 | 7.9.2022 | 5.9.2018 |
| 2018 | 8.9.2022 | 6.9.2018 |
| 2019 | 6.9.2022 | 3.9.2019 |
| 2019 | 7.9.2022 | 4.9.2019 |
| 2019 | 8.9.2022 | 5.9.2019 |
Then you have the departure date table. Compyear, DepartureDate and CompyearSame are static, but the rest are measures:
| Compyear | DepartureDate | COMPYEARSAMEDATE | MinYear | YearsToAdd | SameDate |
| 2018 | 30.12.2022 | 30.12.2018 | 2022 | 0 | 30.12.2018 |
| 2018 | 31.12.2022 | 31.12.2018 | 2022 | 0 | 31.12.2018 |
| 2018 | 1.1.2023 | 1.1.2018 | 2022 | 1 | 1.1.2019 |
| 2018 | 2.1.2023 | 2.1.2018 | 2022 | 1 | 2.1.2019 |
| 2019 | 30.12.2022 | 30.12.2019 | 2022 | 0 | 30.12.2019 |
| 2019 | 31.12.2022 | 31.12.2019 | 2022 | 0 | 31.12.2019 |
| 2019 | 1.1.2023 | 1.1.2019 | 2022 | 1 | 1.1.2020 |
| 2019 | 2.1.2023 | 2.1.2019 | 2022 | 1 | 2.1.2020 |
For this table we do not have to shift the dates but we have to make a measure to see whether we have to add another year to the measure "SameDate" or not based on whether we are filtering on a one or two years. MinYear=YEAR(Min(departureDate)) and YearsToAdd = Year(departureDate) -MinYear.
SameDate = DATEADD(CompYearSameDate,YearsToAdd,Year)
And then finally we have the Sales figures for the year of 2018 and 2019 (numbers scrambled and filtered based on the criteria I have mentioned above):
| Date of sale | Departure date | Sold Amount |
| 4.9.2018 | 30.12.2018 | 50 |
| 4.9.2018 | 31.12.2018 | 60 |
| 4.9.2018 | 1.1.2019 | 70 |
| 4.9.2018 | 2.1.2019 | 80 |
| 5.9.2018 | 30.12.2018 | 55 |
| 5.9.2018 | 31.12.2018 | 66 |
| 5.9.2018 | 1.1.2019 | 77 |
| 5.9.2018 | 2.1.2019 | 88 |
| 6.9.2018 | 30.12.2018 | 61 |
| 6.9.2018 | 31.12.2018 | 73 |
| 6.9.2018 | 1.1.2019 | 85 |
| 6.9.2018 | 2.1.2019 | 97 |
| 3.9.2019 | 30.12.2019 | 67 |
| 3.9.2019 | 31.12.2019 | 80 |
| 3.9.2019 | 1.1.2020 | 93 |
| 3.9.2019 | 2.1.2020 | 106 |
| 4.9.2019 | 30.12.2019 | 73 |
| 4.9.2019 | 31.12.2019 | 88 |
| 4.9.2019 | 1.1.2020 | 102 |
| 4.9.2019 | 2.1.2020 | 117 |
| 5.9.2019 | 30.12.2019 | 81 |
| 5.9.2019 | 31.12.2019 | 97 |
| 5.9.2019 | 1.1.2020 | 113 |
| 5.9.2019 | 2.1.2020 | 129 |
Now I just need a measure which sums up both for each "compyear". So in this example 2018 = 861, 2019 = 1145.
If you have a better solution to my thinking I would love that, DAX is not my strongest side but that is how I think it would be done nicest
