Forum Discussion

Reigning_data's avatar
Reigning_data
Frequent Visitor
6 years ago
Solved

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.

 

 

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    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

2 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    6 years ago

    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's avatar
      Reigning_data
      Frequent Visitor
      6 years ago

      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!