Dynamic Column based on slicer

[thampton]

I have a table with a sales date (per order) and then a disconnected date table. I would like to be able to calculate a dynamic aging based on the slicer selection. 

For example, sales order date is 6/07/2019, if i select 6/11/2019, the column would calculate 4. If i select 6/09/2019, the column would calculate 2. 

Is there any way to do this? 

[Cmcmahan · Accepted solution]

Does this answer look close to what you want? https://community.powerbi.com/t5/Desktop/Dynamic-Column-Calculation-Based-on-Multiple-Slicers/td-p/1...

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.

[thampton]

@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?

[Cmcmahan]

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.

[Cmcmahan]

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.

[thampton]

@Cmcmahan 

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."

[Cmcmahan]

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 try 

SELECTEDVALUE(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