cancel
Showing results for
Did you mean:
Helper I

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

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 🙂

1 ACCEPTED SOLUTION
Super User

@C-Jac , Create new column in values table

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

3 REPLIES 3
Super User

@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``````

 Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! DAX is simple, but NOT EASY!
Super User

@C-Jac , Create new column in values table

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

Helper I

Thank you, this works! 😄

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors