Forum Discussion
Make calculated column based on a variable user-selected date
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.
Actually... thinking that this may be impossible, if one cannot create new columns based on measures, only further measures.
Is there a way of doing this without using measures at all?
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.