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.
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.