Making relationship between Two Date Series

[Anonymous]

Hello,

Below are the two visuals, I want to create single visual combining them. There are two tables with dates (multiple values) and losses. How can I create a relationship, so that user can get visual- Chart 3, where he can drill down from Year- Quarter- Month- Day- Day of the hour. Or if Date-Hierarchy is not possible, how to can I create Chart3 without drill downs.

Columns: ID (unique), Date, hour of Day, loss in Min (all columns have duplicates)

Columns: ID (unique), Date2, hour of Day, loss in Min (all columns have duplicates)

Chart 3

What I need is a way to aggreagate "Loss in Min" by Date fields in both tables and create a single visual. If I put relation on ID columns, I get wrong values. and if I try putting many-many relation on "Date" column nothing is shown.

[C2]

 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  

[bcdobbs]

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/

[Anonymous]

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/

---

[bcdobbs]

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.

[Anonymous]

Hi,

Below link has PBIx file and two csv files with dummy data with exact structure. It would be awesome check it out.

Demo Links

---

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

---

[bcdobbs · Accepted solution]

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.

[Anonymous]

Thanks !! This is what I was looking for.