Use a Date Slicer's values in a Calculation

[Anonymous]

Hello,

I'm trying to use the values put into a date slicer in other calculations regardless of the data the slicer is tied to.

Example:

I have a date slicer and I put in dates 8/1/2019 to 8/15/2019. I have only have data for a product from 8/1/2019 to 8/10/2019. However, I want to use the 8/15/2019 in a calculation.

I know I can use the following to get the max date:

MaxDate =
CALCULATE(MAX(Table[Field]), ALLSELECTED(Table))

However, since my data only goes from 8/1/2019 to 8/10/2019, the value that will be returned is 8/10/2019. I'd like to grab the 8/15/2019 value for calculation.

Basically, I'm trying to calculate average daily usage of a product over a time span. So if I have 10 usage records and the last usage records was 8/10/2019, I still want to calculate the average daily usage from 8/1/2019 to 8/15/2019, thus, that is why I want the value the was typed in by the user. I hope this makes sense.

[jdbuchanan71 · Accepted solution]

Hello @Anonymous 

If you add a date table into your model then join it to your Table you can pull the date from the date table for your slicer and since 8/15/2019 does exist in the date table 

MaxDate =
CALCULATE ( MAX ( Dates[Date] ), ALLSELECTED ( Dates ) )

will return 8/15/2019.

Here is some DAX code for a simple Dates table to start with.

Dates = 
VAR DateRange = CALENDARAUTO()

RETURN
ADDCOLUMNS(
    DateRange,
    "Year",YEAR ( [Date] ),
    "Month", FORMAT ( [Date], "mmmm" ),
    "MonthNum", MONTH ( [Date] ),
    "Month Year", FORMAT ( [Date], "mmm-yyyy"),
    "MonthYearNum", YEAR ( [Date] ) * 100 + MONTH ( [Date] ),
    "Quarter Year", "Q" & FORMAT ( [Date], "q-yyyy" ),
    "QtrYearNum", YEAR ( [Date] ) * 100 + VALUE ( FORMAT ( [Date], "q" ) )
)

[Anonymous]

Thanks for your response.  I didn't implement it exactly as you have here but you got me on the right track.  I was able to create a calendar table, add it to my model, and relate it back to my original data table.