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 on...
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)
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)
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Actually I do have a calendar table called 'Date' and it has a column called Dates which is linked to a fact table called 'Deals' and it's column 'lost_time'.

    Thanks again for the help.  I'll try to apply the formula you provided.