Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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

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

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

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

    Hi Anonymous ,

    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

6 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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. 😕

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    1 year ago

    Hi Anonymous 

     

    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.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

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

    Hi Anonymous ,

    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