User Slicer date value in DAX expression

[Zahoul]

Hello,

I've been struggling with this issue, but i'm afraid there is no solution.

I have a Sales table with different lines with each a SaleDate column.

In my report, I use a date slicer to filter the results based on the SaleDate column.

For instance I filter from the 1st to the 30th of November. I would like to use those 2 values in a DAX expression to evaluate the performance of sales.

I've seen things llike using MIN and MAX of Sales[SaleDate] but unfortunately, i might not have values in november for the 1st of November or the 30th of november. I might have only values between the 10th and the 20th,but I still need to evaluate the performance for the period of the 1st > 30th.

I don't know if I am clear enough, but basically I need to use the 2 values of the date slicer in a DAX expression.

Would it be possible?

Thank you for your help,

Julien

[AlB]

Hi @Zahoul

I'm quoting you:

I might have only values between the 10th and the 20th,but I still need to evaluate the performance for the period of the 1st > 30th

What does that mean exactly??

.

If I understand correctly you're using the Date field in Sales for the slicer.  Do you have a 'Date' table?

[Zahoul]

Hello,

What does that mean exactly??

> It was obvious I was not clear enough 😃

Let's say I have the following Sale table:

Sale ID	Sale Username	Sale Date
1	Bob	05/03/2018
2	Ryan	15/03/2018
3	John	24/03/2018

 If I use my filter from the 1st to the 31th of march, and I want to use these 2 values in a DAX formula, the MIN('Sale'[Sale Date]) will give me 05/03/2018. The MAX will give me 24/03/2018.

Is there a way to get 01/03/2018 and 31/03/2018 in the DAX expression?

No I do not have a separate table Date. What do you mean exactly? How should I fill in this table?

Thank you for your help,

Julien

[AlB · Accepted solution]

@Zahoul

When you talk about your filter, is that a slicer you are using? What field  are you using for that slicer? If you use [Sales Date] you will not be able to select the dates that are not present in [Sales Date], I believe.

In any case, you'd need a 'Date' table with all days and create a relationship between 'Date'[date] and YourTable[Sales Date]. You then use 'Date'[date], which has all dates, in the slicer, to filter. Because of the relationship, it will filter your fact table too.

Google for Date/Calendar tables in DAX or similar to read about it      

[Zahoul]

Crystal clear, thank you!

[AlB]

@Zahoul

You can use CALENDARAUTO( ) for instance to create a Date table