Forum Discussion
User Slicer date value in DAX expression
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
Anonymous
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
5 Replies
- AlB
Community Champion
Hi Anonymous
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?
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
Anonymous
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