Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
C-Jac
Helper I
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   
101/01/2021
202/01/2021
....
420/01/2021
521/01/2021

 

Values table: 

Value   DateID   InPast7Days   

500

10
-40020
90051

 

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
amitchandak
Super User
Super User

@C-Jac , Create new column in values table

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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

4 REPLIES 4
CNENFRNL
Community Champion
Community Champion

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

amitchandak
Super User
Super User

@C-Jac , Create new column in values table

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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

thank you .ıt works

 

Thank you, this works! 😄 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.