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...
  • v-lili6-msft's avatar
    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 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