Forum Discussion
Dynamic Column based on slicer
Does this answer look close to what you want? https://community.powerbi.com/t5/Desktop/Dynamic-Column-Calculation-Based-on-Multiple-Slicers/td-p/107480
If so, you can use their answer, but instead of summing the values, you should be able to use something like this:
MONTHDIFF = CALCULATE (DATEDIFF(Table1[OrderDate], EARLIEST(Table2[SelectedDate]), month ), ALLSELECTED () )
Set your slicer to select values from your independant date table, and you should be good to go.
Does this answer look close to what you want? https://community.powerbi.com/t5/Desktop/Dynamic-Column-Calculation-Based-on-Multiple-Slicers/td-p/107480
If so, you can use their answer, but instead of summing the values, you should be able to use something like this:
MONTHDIFF = CALCULATE (DATEDIFF(Table1[OrderDate], EARLIEST(Table2[SelectedDate]), month ), ALLSELECTED () )
Set your slicer to select values from your independant date table, and you should be good to go.
Cmcmahan Thanks for the reply! For some reason, it is not allowing me to use column in the datediff function; It doesnt pull up the column list. Any ideas?
What happens when you just type in the column manually? Do you get an error message?
I've found that sometimes the autocomplete doesn't fill in, but you can still accomplish the task.Ah, I figured out the issue after trying it myself. The issue is that Power BI doesn't know which row you're referring to when creating the measure, so it assumes it will get multiple results. The easiest way to fix this is to use one of the aggregation functions to turn a column into one value.
Here I used SELECTEDVALUE, which will return whatever the value is if there's only one option, otherwise it will return a blank by default.MONTHDIFF = CALCULATE (DATEDIFF(SELECTEDVALUE(Table1[OrderDate]), EARLIEST(Table2[SelectedDate]), month ), ALLSELECTED () )
When you're only using one date (like in a table/matrix) the filtering is already done for you, so there is only one value to select from. If you're doing something else with your data, you can use an AVERAGE, EARLIEST, or some other aggregation function as fits the scenario.
