Forum Discussion

Doyou21's avatar
Doyou21
Frequent Visitor
6 years ago
Solved

Summary Table Columns W/ Date Previous Date Filter

Im trying to create a new summary table...

 

Having trouble with last 3 columns that I need to filter to the same day for the past 3 wks (7, 14, 21 Days ago).  I've tried using the the column and a measure but can't seem to get the DATEADD filter for these days to work as I get all blanks as the current values. 

 

I'll be using these columns later to calculate the Average and Standard Dev for the 3 days from the current day.  Thanks in advance for the help. 

 

Summary_Fact_EDW-Extract (NEW) =
SUMMARIZE
(
'Fact_Extract'
,'Fact_Extract'[CAL_DT]
,'Fact_Extract'[Group_RPT]
,'Fact_Extract'[Device_Type_RPT]
,'Fact_Extract'[Level]
,'Fact_Extract'[WEBSITE_TRAFFIC_SOURCE]

,"Sum_Inqs",
SUM('Fact_EDW-Extract'[Inquiries])
,"Sum_Ratio_Apps",
SUM('Fact_EDW-Extract'[Ratio Apps])
,"Sum_Inqs_1Wk_Ago",
CALCULATE([Inqs Ttl]
,DATEADD('Fact_EDW-Extract'[CAL_DT],-7,DAY))
,"Sum_Inqs_2Wk_Ago",
CALCULATE(SUM('Fact_EDW-Extract'[Inquiries])
,DATEADD('Fact_EDW-Extract'[CAL_DT],-14,DAY))
,"Sum_Inqs_3Wk_Ago",  
CALCULATE([Inqs Ttl]
,DATEADD('Fact_EDW-Extract'[CAL_DT],-21,DAY)))
 
 
 

 

 

Need Help
  • Doyou21's avatar
    Doyou21
    6 years ago

    Thanks Parry2k,

     

    With your "teach a person to fish method", I went back to my Date DIM Table and Added 3 date columns to have the corresponding weeks prior I needed to refference:

     

    CAL_DT - 7 Days Ago = DIM_Date[CAL_DT] - 7
    CAL_DT - 7 Days Ago = DIM_Date[CAL_DT] - 14
    CAL_DT - 7 Days Ago = DIM_Date[CAL_DT] - 21
     
    I then added relationships (inactive) connecting the current date in my FACT table to the new date columns referenced above.
     
    Lastly, I created 3 measures using the new inactive relationships created below:
     
    Inquiries (-7Days) =
    CALCULATE(sum('Fact_EDW-Extract'[Inquiries]),
    USERELATIONSHIP('Fact_EDW-Extract'[CAL_DT],DIM_Date[CAL_DT - 7 Days Ago]))
     
    Inquiries (-14Days) =
    CALCULATE(sum('Fact_EDW-Extract'[Inquiries]),
    USERELATIONSHIP('Fact_EDW-Extract'[CAL_DT],DIM_Date[CAL_DT - 14 Days Ago]))
     
    Inquiries (-21Days) =
    CALCULATE(sum('Fact_EDW-Extract'[Inquiries]),
    USERELATIONSHIP('Fact_EDW-Extract'[CAL_DT],DIM_Date[CAL_DT - 21 Days Ago]))
     
    It worked!
     
     

3 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    Doyou21 

     

    I just replied to other post with similar question.

     

    For any date related calculations, it is best practice to have Date dimension in your model. There are many blog posts on how to add Date dimension in your model. Once you have Date table, set relationship between you transaction table and date table and use Date from date table in your measure for these time intelligence calculations.

    • Doyou21's avatar
      Doyou21
      Frequent Visitor
      6 years ago

      Thanks Parry2k,

       

      With your "teach a person to fish method", I went back to my Date DIM Table and Added 3 date columns to have the corresponding weeks prior I needed to refference:

       

      CAL_DT - 7 Days Ago = DIM_Date[CAL_DT] - 7
      CAL_DT - 7 Days Ago = DIM_Date[CAL_DT] - 14
      CAL_DT - 7 Days Ago = DIM_Date[CAL_DT] - 21
       
      I then added relationships (inactive) connecting the current date in my FACT table to the new date columns referenced above.
       
      Lastly, I created 3 measures using the new inactive relationships created below:
       
      Inquiries (-7Days) =
      CALCULATE(sum('Fact_EDW-Extract'[Inquiries]),
      USERELATIONSHIP('Fact_EDW-Extract'[CAL_DT],DIM_Date[CAL_DT - 7 Days Ago]))
       
      Inquiries (-14Days) =
      CALCULATE(sum('Fact_EDW-Extract'[Inquiries]),
      USERELATIONSHIP('Fact_EDW-Extract'[CAL_DT],DIM_Date[CAL_DT - 14 Days Ago]))
       
      Inquiries (-21Days) =
      CALCULATE(sum('Fact_EDW-Extract'[Inquiries]),
      USERELATIONSHIP('Fact_EDW-Extract'[CAL_DT],DIM_Date[CAL_DT - 21 Days Ago]))
       
      It worked!