Forum Discussion
Make calculated column based on a variable user-selected date
Hi all,
Looking for help with a calculated column that takes a user-selected date value, and updates the output/dependent column accordingly.
The calculation is essentially: based on a user selected end date, and a start date 'Column A', calculate how many days from date in Column A to end date.
Currently, the calculation is static: it uses DATEDIFF() between Column A and Jan 1, 2021 to calculate the difference (for every row). The goal is to make it dynamic, ideally by some sort of dashboard visual element that a user can click on, say by selecting a certain date out of a date table. I'm open to using a measure, if that measure can in turn be used to in order to calculate this "Days to End Date" column... but it's important each row has it's own value, as this information will then be used in further calculations for other columns.
Thanks!
E.g.: date chosen: 01/02/2021 would yield:
Column A | Days to end Date
01/02/2021 | 0
01/01/2021 | 1
12/01/2020 | 32
01/05/2021 | -3
Hi Anonymous
Calculated columns are populated when they are created or refreshed. Their data are not able to be changed dynamically according to users' selection in slicers or filters in the report. In order to change the values dynamically according to users' selections, you need a measure to realize it. And the proceeding columns based on this measure should all be realized with measures.
To avoid the visuals being filtered by the slicer directly, you need to have an independent date table in the data model. This date table has no relationships with other tables and is to be used as a parameter table for users to select an end date from.
Then you can create measures like below.
Days to end Date = DATEDIFF(SELECTEDVALUE('Table'[Column A]),SELECTEDVALUE('End Date Table'[End Date]),DAY)Days Completed = IFERROR(IF([Days to end Date]<1, 1, 2), BLANK())Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
5 Replies
HI,
I believe you have seperate date table in the report.
In this case you can create new measure using following formula
Measure =var selecteddate = SELECTEDVALUE('Date'[DateId])RETURN DATEDIFF(MAX('Table'[Column A]),selecteddate,DAY)Then have a matrix - like followsRegards,
Sayali
If this post helps, then please consider Accept it as the solution to help others find it more quickly.
Hi Sayali,
Thanks for the quick response! I've got this working as a standalone measure, but there's an issue when I want to go forward and now use this measure in the calculation of other columns (which is why I was hoping I could just obtain a variable to plug into a column called "Days to End Date").
As an example, my next column is something like
Days Completed = IFERROR(IF("Days to End date"<1, 1, 2), BLANK())
When I use this new "Days to End Date" measure in this column, the first check, IF(days to end date< 1) appears to prove true every time, returning 1 every time... in reality, as with the above test values (when days to end is 32, 1, etc) it *should* return 2, not 1.
I am using a unique identifier for the row in my table/visual, and I am not summarizing values, if that helps.
parry2k thanks again for your help on my first question... let me know if you have any thoughts on this one; or any tutorials that would help! 😁
