Date Categorical Slicer

[lguima]

I have three tables:

1) Date Dimension

2) Fact Table with Sales data

3) A Events Dimension with important events that happend in the world. Theses Events have a date associated to it:

I created a Slicer and i need to filter only the data after the selected event in Slicer:

I'm not sure if it's the right way to do that, but I created a dimension inside fact called: is_event

is_event = 
    IF(MIN(dim_date[Date])>=MIN(dim_events[Event Date]), 1, 0)

After that, my idea is to use filters to get only data with is_event = 1.

The problem is that this formula is not working, the filter is not applied. I'm sure the problem is related to this MIN formula, but I need to use some aggregation to get data from another table.

Could someone show me an example of how to make this type of slicer? Could even be a different approach.

[AllisonKennedy · Accepted solution]

@lguima 

You could use something similar to what I have done in this blog with approximate lookup- you'll need to make sure your events table is NOT related to any other tables. 

https://excelwithallison.blogspot.com/2020/06/dax-approximate-lookup.html 

[milodinosaur]

Hi @AllisonKennedy

I tried your solution but it seems to not be working for me and was just wondering if you could help me with it.

This is the main idea of my page:

As you can see, I have 2 date slicers that control the 2 lines on the line chart. However, I would also love to have an event selection that allows me to filter my date for the first date table (ie the one on the left) to the corresponding start date of the event, on top of the date slicers. Hence, I would still require the date slicers since the 'default' mode of date selection would still be the slicers, and the events slicer is an add on. 

Would you have any ideas how should I go about solving this? 

[AllisonKennedy]

@milodinosaur are you still trying to tackle this? It gets tricky when you want to give users two options to filter the same field (which is essentially what you're trying to do). You could potentially use the event drop down to filter only the date slicer, using 'edit interactions' and the approximate lookup method, but users will still need to select the dates in the slicer itself to update the chart. 

[Orezaremu99]

I have a power bi problem. I am trying to sync 2 slicers. They are both referring to dates and one is in text format e.g. December 2022. The other is in date format e.g 31 December 2022. If I try to sync both slicers an additional date appears on the slicer I.e. on the slicer with the text format for example , if there is a list of November 2022, December 2022 etc syncing both slicers adds to my list and you will see something Like November 2022, December 2022 , 31 December 2022 hence it doesn't see n 31 December 2022 as the same as December 2022 and cannot sync them. How can I fix this ? 

[AllisonKennedy]

@Orezaremu99  What table(s) are you getting your text and date dates from? If they are in the same table, or directly related, then you don't need to use 'sync slicers', the text slicer will automatically filter the date slicer. 

[AllisonKennedy · Accepted solution]

@lguima You inspired me to write a blog. Hope you find these two links helpful:

https://excelwithallison.blogspot.com/2021/08/categorical-date-slicer-in-power-bi.html 

https://excelwithallison.blogspot.com/2020/09/reporting-order-of-operations.html 

Also see attached the report below signature.

[AllisonKennedy · Accepted solution]

@lguima 

You could use something similar to what I have done in this blog with approximate lookup- you'll need to make sure your events table is NOT related to any other tables. 

https://excelwithallison.blogspot.com/2020/06/dax-approximate-lookup.html 

[lguima]

WOW! You are fantastic!

I'm new to PowerBI and was used to work with other tools, so I'm missing SQL and MDX.

Just one final question, do you have an example of how to make it get also the data from previous 12 months before the event date?

The idea is to compare the impact of event in previous 12 month and next 12 months!

Thanks!

[AllisonKennedy]

@lguima  Yes, you can use EDATE function to add or subtract as many months as you want from the event date. 

Sales After Selected Event with Buffer =

CALCULATE([Total Sales], FILTER('Calendar', 'Calendar'[Date]>EDATE([Selected Event Date], 12 ) ))

In the attached file I have gone one step further and added a parameter so you can choose the number of months on either side of the date:

Sales After Selected Event with Buffer =

CALCULATE([Total Sales], FILTER('Calendar', 'Calendar'[Date]>EDATE([Selected Event Date], [Month Buffer Value]) ))