Dynamic Date in the Future

[Reigning_data]

Hi all,

I have tried to look for an answer to this question, but I keep coming up blank.

I need to reference a date in the future, based off of TODAY ( ). The date needs to be dynamic and I'd prefer not to just add x days.

I am trying to calculate the number of weeks between today and a future date within a calculation. See underlined below.

e.g. - DATEDIFF ( TODAY ( ) , (TODAY ( ) + 3 MONTHS)  , WEEK )

I had thought I could do this with DATEADD, but it seems that this is looking for a column reference.

Ideally, I'd be able to make the integer in the + 3 MONTHS  a variable, which can then be selected by the user through the use of a switch. I'm confident I'm able to do this part, but figured I'd include it, in case it alters any responses.

[v-lili6-msft · Accepted solution]

hi @Reigning_data 

First, you should know that:

1. Calculation column/table not support dynamic changed based on filter or slicer.
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result.

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

Then for your case, you could use 

What if parameter

for example:

Step1:

There should be a date table in the report, if not, you could try this formula to create one.

Date = CALENDAR(TODAY()-365,TODAY()+365)

Step2:

Create a what if parameter

Step3:

If you just use (today+ days) as a variable, you could try this simple formula to create a measure

Measure 1 = DATEDIFF(TODAY(),TODAY()+[Parameter Value],WEEK)

Then just use slicer to select dynamic dates.

If you want use (today+months) as  a variable, you need to use this formula to create a measure

Measure 2 = var _table= ADDCOLUMNS('Date',"_movedate",DATEADD('Date'[Date],[Parameter Value],DAY)) return
DATEDIFF(TODAY(),MAXX(FILTER(_table,[Date]=TODAY()),[_movedate]),WEEK)

here is sample pbix file, please try it.

Regards,

Lin

[Reigning_data]

Hi Lin,

Thank you so much for your detailed reponse! I've been maunally writing my what-if tables all this time. Never knew that's what that button did...

Measure 2 is the real gold-mine for me, though.

Thanks!