Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

dax formula date range including today

I need help defining a column to show Today's date minus 7 days and include all dates within that range.  I've created the following columns, hoping I would find a way to get the correct syntax in one column but haven't been able to.   NOTE:  I've looked through all of the posts for ideas but nothing has worked so far.  

 

  • Today = TODAY()
  • Date = CALENDAR("2019-Jan-1", TODAY())
  • OneWeekAgo = 'Date'[Dates]-7  <-- this only gives me the date as of 7 days prior (not a range)
 

 

 

Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    7 years ago

    You will need a calender table hooked to your fact table but then you can use a calculated column like this in your fact table.

    Dates List = 
VAR RowDate = table[table date]
RETURN CALCULATE(CONCATENATEX(DATES,DATES[Date],"| "),DATES[Date]<=RowDate,DATES[Date]>=RowDate-7)

4 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    7 years ago

    Somthing like this?

    Last 7 dates = 
CALCULATE(CONCATENATEX(DATES,DATES[Date]," | "),DATES[Date]<= TODAY() && DATES[Date] >= TODAY()-7)

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks for the help but the formula doesn't seem to work for me (and I've tried a few variations). 

       

      Also, I need my data in a column form rather than a row so CONCATENATEX may not work (since it outputs a row of dates).

       

      Actual table and column name is:

      • Table name is 'deals'
      • Column name is 'lost_time'
      • jdbuchanan71's avatar
        jdbuchanan71
        Icon for Super User rankSuper User
        7 years ago

        You will need a calender table hooked to your fact table but then you can use a calculated column like this in your fact table.

        Dates List = 
VAR RowDate = table[table date]
RETURN CALCULATE(CONCATENATEX(DATES,DATES[Date],"| "),DATES[Date]<=RowDate,DATES[Date]>=RowDate-7)