How do i make a simple dateslicer for active rows?

[StanTheMan]

Hi guys,

I'm struggling with an issue on filtering dates/ creating simple slicer for dates.
In the related table i have a start and end date. The customer wants to know which rows are active in a certain period of time, let's say a quarter for this instance. The solution i came up with is creating two seperate slicers, one for filtering the startdate and one for the end date. So the two serparate slicers would look like this:

Startdate:
--/--/--  |  31-12-2018

Enddate:
1-10-2018  |  --/--/--

This returns all the active rows of the table for Q4 2018, the problem with this solution is that the customer finds it difficult to understand. They prefer a checkbox in which they can just choose the quarter they want the active rows for. Does anyone know how to achieve this with one filter for start and end date?

Kind regards,
Stan

[v-frfei-msft · Accepted solution]

Hi @StanTheMan,

I made one sample for your reference. If it doesn't meet your requirement, kindly share your sample data and excepted result to me.

1. Create a date table and one calculated column.

date = CALENDARAUTO()

YQ = FORMAT('date'[Date],"yyyy") & "-Q" & FORMAT('date'[Date],"Q")

2, Create a measure as below and make the table visual filtered by it.

Measure = var maxdate = CALCULATE(MAX('date'[Date]),ALLSELECTED('date'))
var mindate = CALCULATE(MIN('date'[Date]),ALLSELECTED('date'))
var enddate = MAX(Table1[End date])
var star = MAX(Table1[Start date])
return
IF(ISBLANK(enddate),1,IF(enddate>=maxdate,1,0))

For more details, please check the pbix as attached.

Regards,

Frank

[Anonymous]

Hey Stan, 

Do you happen to have a date table in your model? If not, click the "new table" button under the "Modeling" tab and add this DAX code:

Date = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2017, 1, 1 ), TODAY () ),
    "Year", YEAR ( [Date] ),
    "Monthnumber", FORMAT ( [Date], "MM" ),
    "YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
    "YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
    "MonthNameShort", FORMAT ( [Date], "mmm" ),
    "MonthNameLong", FORMAT ( [Date], "mmmm" ),
    "DayOfWeekNumber", WEEKDAY ( [Date] ),
    "DayOfWeek", FORMAT ( [Date], "dddd" ),
    "DayOfWeekShort", FORMAT ( [Date], "ddd" ),
    "Quarter", "Q" & FORMAT ( [Date], "Q" ),
    "YearQuarter", FORMAT ( [Date], "YYYY" ) 
        & "/Q"
        & FORMAT ( [Date], "Q" )
)

There is probably more than what you require in this date table, however the last column added is potentially what you are looking for. Make sure you indicate that this table is indeed a date table - you can do this by right clicking on the table name to the far right of the screen and going to "mark as date table". 

Connect your main table that has all the data a date (many to 1 relationship) to the newly created date table in the model builder.  From there you can create 1 slicer using the "Quarter" column that was made in the date table. Change the formatting to checklist and that should do it!

Hopefully I've read your question correctly and that this explaination makes sense. 

Thank you, 

Derek G

[StanTheMan]

Hey Derek,

Thanks for the reply, I forgot to mention that already tried using a date table.
For me this solution only works if you have one date in the fact table, and in this situation i have two (start and end date) and need to combine them to get the right rows. 

But still, correct me if i'm wrong, if you think this is still posible using a Date Table dimension, i would love to hear it!
(i'm already using it in my model anyway)

The solution of @v-frfei-msft works for me!

Thank you,
Stan

[Anonymous]

Hey!

I tried to think of any other solution however the solution provided by @v-frfei-msft will be your best bet. Because you have two date fields in your fact table and need to combine them, measures are the way to go. Anything that I think of will either convolute your data model or create many-to-many relationships which aren't ideal.

My appologies that I couldn't be anymore helpful! 

Derek G