Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Making relationship between Two Date Series

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 v...
  • bcdobbs's avatar
    bcdobbs
    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.