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.Rowcategorysubcategorybeginning of monthbeginning of yeartotal_recordsunique_usersCOMMENT1NULLNULLNULLNULL8388748689293no grouping, everything ever all time2PNULLNULLNULL30029258746aggregated only by category P3MNULLNULLNULL3971823435387aggregated only by category M4ANULLNULLNULL9766816062aggregated only by category A5CNULLNULLNULL30220953729aggregated only by category C6XYNULLNULL8361178172947aggregated by category X and subcategory Y, nothing else7NULLNULLNULL1/1/20243413517842aggregated by year (2024)8MG3/1/20251/1/20253971823435387aggregated by category M, subcategory G, year 2025, month 3/259NULLNULL8/1/20251/1/20258355225687139aggregated by year (2025) and month 8/2510PNULLNULL1/1/202530029258746aggregated by category P, year 202511CNULLNULL1/1/202530220953729aggregated by category C, year 2025I 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 areDate.Date = CubeTable.BeginningOfMonthDate.Date = CubeTable.BeginningOfYearMeasures: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.

Here’s the refined version of your measure:Unique Members by Month =CALCULATE( SUM('App Cube'[unique_users]), USERELATIONSHIP('App Cube'[BeginningOfMonth], Dates[Date])) Unique Members by Year =CALCULATE( SUM('App Cube'[unique_users]), USERELATIONSHIP('App Cube'[BeginningOfYear], Dates[Date]))If you want dynamic switching (Month vs Year) in one measure, use:Unique Members Dynamic =SWITCH( TRUE(), SELECTEDVALUE(Dates[Granularity]) = "Month", CALCULATE(SUM('App Cube'[unique_users]), USERELATIONSHIP('App Cube'[BeginningOfMonth], Dates[Date])), SELECTEDVALUE(Dates[Granularity]) = "Year", CALCULATE(SUM('App Cube'[unique_users]), USERELATIONSHIP('App Cube'[BeginningOfYear], Dates[Date])))

mateoc15

Advocate II

10 months ago

Solved

Multiple Dates in Cube Table - relationship to Date table

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.

Need Help

RonaldBalza-943
10 months ago
Here’s the refined version of your measure:

Unique Members by Month =
CALCULATE(
    SUM('App Cube'[unique_users]),
    USERELATIONSHIP('App Cube'[BeginningOfMonth], Dates[Date])
)

Unique Members by Year =
CALCULATE(
    SUM('App Cube'[unique_users]),
    USERELATIONSHIP('App Cube'[BeginningOfYear], Dates[Date])
)

If you want dynamic switching (Month vs Year) in one measure, use:
Unique Members Dynamic =
SWITCH(
    TRUE(),
    SELECTEDVALUE(Dates[Granularity]) = "Month",
        CALCULATE(SUM('App Cube'[unique_users]), USERELATIONSHIP('App Cube'[BeginningOfMonth], Dates[Date])),
    SELECTEDVALUE(Dates[Granularity]) = "Year",
        CALCULATE(SUM('App Cube'[unique_users]), USERELATIONSHIP('App Cube'[BeginningOfYear], Dates[Date]))
)

Forum Discussion

Multiple Dates in Cube Table - relationship to Date table

3 Replies

Related content

Date Relationships - Multiple Tables, Multiple dates.

Fact Table Multiple Dates Relationship with Dimension Date

Date relationship issues

Multiple Date Fields

Relationship