Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Reigning_data
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
v-lili6-msft
Community Support
Community Support

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

View solution in original post

2 REPLIES 2
v-lili6-msft
Community Support
Community Support

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

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!

Helpful resources

Announcements
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

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