Forum Discussion

bwelsh's avatar
bwelsh
Helper I
9 years ago
Solved

Working with 2 time dimensions

So I have two seperate quality measures I track from week to week.    Quality Measure 1 - Weekly Quality is measured Sunday - Saturday Quality Measure 2 - Weekly Quality is measured Thursday - Wed...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    Hi bwelsh

     

    One idea is to have a single date table (with one row per date), but have a column for each Week ending

     

    Something like this calculated table :

     

    Dates = ADDCOLUMNS(
            CALENDAR(DATE(2016,1,1) , TODAY()) ,
            "Weekday Name" , FORMAT([Date],"DDDD") ,
            "Week Ending Saturday" , [Date] + (7-WEEKDAY([Date])) ,
            "Week Ending Wednesday" , VAR X = 7-MOD(WEEKDAY([Date])+3,7)RETURN [DATE] + IF(X=7,0,X)
            )

     

     

    Then just connect the Date column to your fact table and use  the Week Ending columns on your Axis

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
9 years ago

Hi bwelsh

 

One idea is to have a single date table (with one row per date), but have a column for each Week ending

 

Something like this calculated table :

 

Dates = ADDCOLUMNS(
            CALENDAR(DATE(2016,1,1) , TODAY()) ,
            "Weekday Name" , FORMAT([Date],"DDDD") ,
            "Week Ending Saturday" , [Date] + (7-WEEKDAY([Date])) ,
            "Week Ending Wednesday" , VAR X = 7-MOD(WEEKDAY([Date])+3,7)RETURN [DATE] + IF(X=7,0,X)
            )

 

 

Then just connect the Date column to your fact table and use  the Week Ending columns on your Axis

bwelsh's avatar
bwelsh
Helper I
9 years ago

This is what I was thinking, thanks for the confirmation!