topic Re: Calculated column if date is within last 7 days using Calendar Dimension in DAX Commands and Tips

Calculated column if date is within last 7 days using Calendar Dimension

C-Jac — Mon, 22 Feb 2021 16:02:22 GMT

I have two tables. One is a Calendar Dimension the other has values in it. They are linked by an ID, and I need to create a 'flag' to determine whether the value row is in the past 7 days, but I can't seem to get the relationship to work. I tried using USERELATIONSHIP and RELATED and RELATEDTABLE, but maybe I'm unable to get it to work.

Calendar Dimension:

Id	Date
1	01/01/2021
2	02/01/2021
..	..
4	20/01/2021
5	21/01/2021

Values table:

Value	DateID	InPast7Days
500	1	0
-400	2	0
900	5	1

What i want is to use the relationship, to add the column with the 0's if the date is not within the past 7 days, and 1 if it is within the past 7 days. Hope this makes sense, and that you can help 🙂

Re: Calculated column if date is within last 7 days using Calendar Dimension

amitchandak — Mon, 22 Feb 2021 16:36:19 GMT

C-Jac , Create new column in values table

if(related([Date]) >= today()-7 && related([Date]) <= today(),1,0)

Re: Calculated column if date is within last 7 days using Calendar Dimension

CNENFRNL — Mon, 22 Feb 2021 21:29:23 GMT

C-Jac , make sure a relationship is correctly established between two tables by this means: Calendar[ID] (1:*) Values[DateID]; then you might want to try adding a calculated column in Values table,

InPast7Days = VAR __dt = RELATED ( Calendar[Date] ) RETURN ( TODAY () - 7 <= __dt && __dt <= TODAY() ) + 0

Re: Calculated column if date is within last 7 days using Calendar Dimension

C-Jac — Wed, 24 Feb 2021 12:40:38 GMT

Thank you, this works! 😄

Re: Calculated column if date is within last 7 days using Calendar Dimension

Muhammetmuslu — Wed, 24 Jan 2024 11:33:31 GMT

thank you .ıt works