Need help with filtering two dates using a single slicer in Power BI

[Gandhia]

Hi,

I need help with my Power BI report, where I would like to use a single slicer to filter data based on dates. I have a table with fields like Start Date and End Date, and I want to use the slicer to allow the user to select a date range. Based on that, all rows in the table where the date falls within the selected range should be filtered.

I also have a separate calendar table (Date), but I have not been able to get the slicer to work in a way that it correctly filters based on the Start Date and End Date fields. The filtering should work with just one slicer so that if a record's date falls within the selected range, it will appear in the report.

Is there anyone who could advise how this should be implemented or how I can achieve this in Power BI?

I have tried many different options, but seems like nothing does work. I have also tried to use two different slicers, but 1. It doesn't work neither (not all data shows in page's visuals) and it isn't the best solution either.
If you have any questions, feel free to ask.

More information:
Slicer with ONE date (End Date and slicer type before) works.
But start date and after doesn't work -> When I choose some date and move back to the starting date where the slicer was at the beginning, there are NOT all the data what there should be (they will appear back when I delete slicer..) I don't understand this at all.. 

Thank you in advance for your help!

-G

[Anonymous · Accepted solution]

Hi all,thanks for the quick reply, I'll add more.

Hi @Gandhia ,

Regarding your question, I don't think there should be a relationship between the fact table and the time dimension table. You seem to have created a table-to-table relationship between the 'Date' column of the fact table and the time dimension table. This will result in only filtering the 'Date' column and not the 'StartDate' & 'EndDate' columns. My suggestion is to remove the relationship between the two tables.

The Table data is shown below:

Use the following DAX expression to create a measure

Measure = 
VAR _startDate = MIN('DateTable'[Date])
VAR _endDate = MAX('DateTable'[Date])
VAR _factTableDate = SELECTEDVALUE('Table'[Date])
VAR _factTableStartDate = SELECTEDVALUE('Table'[Start Date])
VAR _factTableEndDate = SELECTEDVALUE('Table'[End Date])
RETURN 
IF(
    (_factTableDate >= _startDate && _factTableDate <= _endDate) &&
    (_factTableStartDate >= _startDate && _factTableStartDate <= _endDate) && 
    (_factTableEndDate >= _startDate && _factTableEndDate <= _endDate),
    1,BLANK()
)

 Power BI automatically hides measure that result in 'Blank', which gives the correct result.

Turn off the 'Text wrap' option in the table visual 

Move the mouse over the marker and the '<-||->' symbol will appear, drag it.

Final output

Best Regards

[danextian]

Hi @Gandhia 

You can't be using a related dates table for that as you'll be able to filter only by either the start or end date. You will need to create a measure for that.

Count by Time Period = 
VAR StartDate =
    MIN ( Dates[Date] )
VAR EndDate =
    MAX ( Dates[Date] )
RETURN


    COUNTROWS (
        FILTER (
            Contracts,
            Contracts[Date Start] <= EndDate
                && Contracts[Date End] >= StartDate
            )
        )

Using the measure above, you can see below that contract code AA009Contract1 appears in 2018-01 to 2018-12 as its start and end dates are within these periods. Please see the attached sample pbix.

[amitchandak]

@Gandhia , Please check if my blog and video on the same topic can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4

Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU

[Gandhia]

Thank you for your response. Unfortunately I think it doesn't help me with my problem at all. At least I don't get how it would help me. 😕

[amitchandak]

@Gandhia , if you need Active between dates, or based on start date and end date it will help. I am attaching the files where this has been done in two manners

[Gandhia]

Hi. Nope. Still not understand. Your slicer 'between' has same problem than I have:

For example see the picture above - the slicer is between 25.11.2016 and 17.2.2017 and the table still has there dates which aren't between those dates (e.g. Start Date 1.1.2015, End Date null) etc.

Or am I missing something ?