Forum Discussion
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...
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] - 7CAL_DT - 7 Days Ago = DIM_Date[CAL_DT] - 14CAL_DT - 7 Days Ago = DIM_Date[CAL_DT] - 21I 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
Super User
Super User
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.
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!