Forum Discussion
Making relationship between Two Date Series
- 4 years ago
Have a look at:
Demo File
I changed the shift date columns to Date type (not date time).I created a date table with:
Date = VAR MinYear = 2021 VAR MaxYear = 2021 RETURN ADDCOLUMNS ( CALENDAR( DATE ( MinYear, 1, 1 ), DATE ( MaxYear, 12, 31 ) ), "Calendar Year", "CY " & YEAR ( [Date] ), "Month Name", FORMAT ( [Date], "mmmm" ), "Month Number", MONTH ( [Date] ), "Month Year", DATE ( YEAR( [Date] ), MONTH ( [Date] ), 1 ), //Format as mmm YYYY "Weekday", FORMAT ( [Date], "dddd" ), "Weekday number", WEEKDAY( [Date] ), "Quarter", "Q" & TRUNC ( ( MONTH ( [Date] ) - 1 ) / 3 ) + 1 )and an Hour table with:
Hour = GENERATESERIES( 1, 24, 1 )(and renamed the single column it creates to Hour of Day)
Linking it all up in data model:
I did have to delete your existing visuals because it wouldn't let me clear existing filters.
However you can put the following on shared axis:A pre built hierachy can't span two tables which is why I've done it this way.
Also best practice would be once you calculate the hour flag in power query throw away the t_stamp column if you no longer use it as it will cause your model to be much bigger.
similar issue here, I have 2 tables 1 is manually created & the other is a direct query table, Im trying to create a relation between Shipped Date from direct query table & Date from the manual forecast table, im able to make the relationship, however when I create a "slicer" with either Shipped Date , or Date it only updates the visuals with date from their tables, please help