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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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)

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)

thank you .ıt works

 

Thank you, this works! 😄 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors