Forum Discussion
Dynamic Date in the Future
- 6 years ago
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
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
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!