cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Frequent Visitor

## Dynamic Date in the Future

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.

1 ACCEPTED SOLUTION
Community Support

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 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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support

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 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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

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!

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors