Hi,
I want to create a simple slicer which includes custom date options, allowing my user to view Sales over their selected period, such as:
I know I can use a Relative Date Slicer, to give my users a lot of options, but it is a bit too busy and doesn't offer the specific options that we only want.
To create the above list, I have simply created a basic table called DateOptions and pointed a Slicer to it. Is there a way using DAX (or any other method!), to check on the selected value and then restrict my Sales dataset based on the selected item? So something like:
If SelectedDateOption= "Today", then filter SalesDate = TODAY() If SelectedDateOption= "Yesterday", then filter SalesDate = TODAY()-1 If SelectedDateOption= "This Month", then filter SalesDate = MONTH(TODAY()) I have seen examples, where you can sum up a value based on a selected slicer option, but I want to filter my entire dataset, so that all visuals and reports etc respond to the chosen item. I am sure I have seen this done, hopefully someone here knows how! Cheers Mark
Hi Mark,
Great question. Chris Web has a post on this here; https://blog.crossjoin.co.uk/2016/05/30/creating-current-day-week-month-and-year-reports-in-power-bi-using-bidirectional-cross-filtering-and-m/
Mynda
Hi Mynda,
Excellent - thank you for the link! I had been struggling with this for a few days and much Googling and a post on the Power BI forums hadn't yielded the solution, despite a few replies.
I haven't gone with the M code approach to dynamically populate my CustomDate table via the function call as I am just not familiar with it. Instead I have created a small table in SQL which will be part of my data model.
Anyway, really pleased to get this working, I was sure it was possible and in hindsight the solution was really quite simple!
Cheers
Mark
