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.
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.
The goal for this would be to select a date in the slicer, have the column calculate the aging, then distribute these into buckets (i can do the bucket in a IF statement).
A problem im runnning into: the SELECTEDVALUE worked for the table column but when i try to select the unrelated date table, it throws the error below. This is the table that is connected to my slicer.
"EARLIER/EARLIEST refers to an earlier row context which doesn't exist."
Try SELECTEDVALUE instead of EARLIEST in your independent table.
You may want to set a default for SELECTEDVALUE so that when you have multiple/no selection you don't get errors.
I would first trySELECTEDVALUE(MONTH(DateTable[Date]))
And if that gives you weirdness, try
SELECTEDVALUE(MONTH(DateTable[Date]), <default value>)
replacing <default value> with a default date of some sort