cancel
Showing results for 
Search instead for 
Did you mean: 
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)



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

View solution in original post

3 REPLIES 3
CNENFRNL
Super User
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 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)



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

Thank you, this works! 😄 

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors