Summary Table Columns W/ Date Previous Date Filter

[Doyou21]

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

[parry2k]

@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 · Accepted solution]

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!

[parry2k]

@Doyou21 glad to hear that you have solution in place.