I have the following tables in my Tabular Model:
Fact Table:
| sales | Example |
|---|---|
| quantity | 50 |
| date | 2024-09-30 |
Date Dimension:
| date | Example |
|---|---|
| date | 2024-09-30 |
| month | 09 |
| year | 2024 |
Relationship: sales.date -> date.date
Resulting in the following view (sum of sales.quantity by date.year):
| date.month | 2022 | 2023 | 2024 |
|---|---|---|---|
| 01 | 50 | 20 | 30 |
| 02 | 30 | 30 | 40 |
| 03 | 20 | 20 | 30 |
| 04 | 10 | 10 | 50 |
| 05 | 20 | 20 | 60 |
| 06 | 50 | 60 | 30 |
| 07 | 30 | 50 | 20 |
| 08 | 50 | 20 | 10 |
| 09 | 60 | 40 | 10 |
| 10 | 30 | 20 | 20 |
| 11 | 20 | 30 | |
| 12 | 10 | 20 | |
| Total | 380 | 340 | 300 |
I need a DAX measure to calculate the sum of sales.quantity year-to-date, but only up until the max available date of the current year – in this case, october 2024.
So the results should be (sum of sales.quantity jan – oct):
- 2022: 350
- 2023: 290
- 2024: 300
Optimally the measure would use time-intelligence functions, so I could additionally create a mesaure for “previous-year” values with SAMEPERIODLASTYEAR
2
here is the sample data I used for testing
| date | quantity |
|---|---|
| 2023/1/1 | 1 |
| 2023/2/1 | 2 |
| 2023/3/1 | 3 |
| 2023/4/1 | 4 |
| 2023/5/1 | 5 |
| 2023/6/1 | 6 |
| 2023/7/1 | 7 |
| 2023/8/1 | 8 |
| 2023/9/1 | 9 |
| 2023/10/1 | 10 |
| 2023/11/1 | 11 |
| 2023/12/1 | 12 |
| 2024/1/1 | 13 |
| 2024/2/1 | 14 |
| 2024/3/1 | 15 |
| 2024/4/1 | 16 |
| 2024/5/1 | 17 |
| 2024/6/1 | 18 |
| 2024/7/1 | 19 |
| 2024/8/1 | 20 |
| 2024/9/1 | 21 |
| 2024/10/1 | fa |
and also create a date table
then create a measure
MEASURE =
VAR _max =
CALCULATE ( MONTH ( MAX ( 'date'[Date] ) ), ALL ( 'date' ) )
RETURN
IF ( MAX ( 'date'[month] ) <= _max, 1 )
add this measure to matrix visual filter and set to 1