Forum Discussion
Making relationship between Two Date Series
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.
Thanks for the reply. I was thinking of using date-hierarchy to navigate from Year to Day. And there is additional column named "Date of the hour"- it has values from 1-24, it will last level. Will this be possible.
and I didn't get this part of your solutions-
bcdobbs wrote:Hi,
You need a date table that has a single row for each date and columns for all the different descriptions like quarters. You then relate that to your two tables through 1 to many relationships. If you then use fields from that date table in visuals they all update together.
You can create date tables in lots of ways but one way is straight in DAX:
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
All possible. Hours of the day changes slightly as wouldn't normally split time and date apart.
In reference to the bit of my explanation a date table just has columns for any date related attribute you want to slice by so for example would have columns:
Date, Start of Month, Quarter, Week Number etc
If you mock up some demo data that matches your existing structure and send a pbix file I'll have a go at implementing so you can see what I mean.
Hi,
Below link has PBIx file and two csv files with dummy data with exact structure. It would be awesome check it out.
bcdobbs wrote:All possible. Hours of the day changes slightly as wouldn't normally split time and date apart.
In reference to the bit of my explanation a date table just has columns for any date related attribute you want to slice by so for example would have columns:Date, Start of Month, Quarter, Week Number etc
If you mock up some demo data that matches your existing structure and send a pbix file I'll have a go at implementing so you can see what I mean.
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.Thanks !! This is what I was looking for.