I need help with relationships betwen multiple dates in a single table and a date dimension table. I've found some articles, but nothing quite fits exactly what I'm looking for. I want to be able to use the Date table as a slicer across multiple visualizations - for instance select 8/1/25 and display both the month of August in one visualization, and display the full year 2025 in another visualzation.
For those who don't know, a cube is a model where a single table contains multiple levels of aggregation. For records where a particular dimension is NOT a part of the aggregation it's just null. In a PBI report, if I wanted to see total records for category P all time I would filter to category = P, subcategory is null, beginning of month is null, and beginning of year is null.
| Row | category | subcategory | beginning of month | beginning of year | total_records | unique_users | COMMENT |
| 1 | NULL | NULL | NULL | NULL | 8388748 | 689293 | no grouping, everything ever all time |
| 2 | P | NULL | NULL | NULL | 300292 | 58746 | aggregated only by category P |
| 3 | M | NULL | NULL | NULL | 3971823 | 435387 | aggregated only by category M |
| 4 | A | NULL | NULL | NULL | 97668 | 16062 | aggregated only by category A |
| 5 | C | NULL | NULL | NULL | 302209 | 53729 | aggregated only by category C |
| 6 | X | Y | NULL | NULL | 8361178 | 172947 | aggregated by category X and subcategory Y, nothing else |
| 7 | NULL | NULL | NULL | 1/1/2024 | 34135 | 17842 | aggregated by year (2024) |
| 8 | M | G | 3/1/2025 | 1/1/2025 | 3971823 | 435387 | aggregated by category M, subcategory G, year 2025, month 3/25 |
| 9 | NULL | NULL | 8/1/2025 | 1/1/2025 | 8355225 | 687139 | aggregated by year (2025) and month 8/25 |
| 10 | P | NULL | NULL | 1/1/2025 | 300292 | 58746 | aggregated by category P, year 2025 |
| 11 | C | NULL | NULL | 1/1/2025 | 302209 | 53729 | aggregated by category C, year 2025 |
I have two dates in the cube table. One is for month (which is the date of the first of the month), and one is year (which is Jan 1 of the year). I have created the relationships and am using WITHRELATIONSHIP in my measures. My date table includes one record per day, and also includes a date for first of the month and first of the year. I'm wondering if the relationship of the cube to date table should be Date.Date (current setup) or Date.FirstOfMonth (which seems to cause more problems since there are about 30 date table records for each FirstOfMonth). My defined relationships are
Date.Date = CubeTable.BeginningOfMonth
Date.Date = CubeTable.BeginningOfYear
Measures:
Unique Members by Month =
calculate(
sum('App Cube'[unique_members]),
USERELATIONSHIP('App Cube'[first of month], Dates[Date])
)
Unique Members by Year =
calculate(
sum('App Cube'[unique_members]),
USERELATIONSHIP('App Cube'[first of year], Dates[Date])
)
Any help is greatly appreciated! Thanks.
