slicer for selection of different date tables

[BORUCH]

Hi
I have the below formula dax below

Total Sales$ Sold From -- to --FDS P1:=CALCULATE([total sales$$],DATESBETWEEN('Calendar'[Date],[Selected_FromP1COMPAREFROMDATE],[Selected_ToP1COMPARETODATE]))

i would like to have an option to select what date table i want, so i have two different date tables in my power pivot data model ,one is connected to my main sales table invoice date the other date table is connected to my main sales table order date
What would be the process of having a slicer to select what date table i want to use in this part of the formula

DATESBETWEEN('Calendar'[Date]

I'm going to assume it will involve some type of disconnected table I'm just not sure how to get it done any help is appreciated

 

[Matt Allington]

Datesbetween (and all time intelligence functions) require you to use the date column of a connected date table to work. The only thing I can conceive “may” work (I’m not sure) is to create both calendar tables with inactive relationships, add both versions of the formula as variable options in the measure and then have a disconnected table with a toggle to choose which gets used.

Pseudo code as follows

Var table1 = calculate([total],datesbetween(<use table 1>,,),userelationship(table1))
Var table1 = calculate([total],datesbetween(<use table 2>,,),userelationship(table2))
Var mySelection = max(disconnectedTable[selection])
Return
Switch(mySelection,
“Table1”,table1,
“Table2”,table2)

I’m not sure if the first to variables need to be double nested in 2 calculates or not.

Var table1 = calculate(calculate([total],datesbetween(<use table 1>,,)’,userelationship(table1))

Try the first. If it doesn’t work, try the second.

That’s my best guess.