Forum Discussion

pva's avatar
pva
Frequent Visitor
4 years ago
Solved

Date filter

Hi all! I have a table with different sprints with their own star and end date. I want to filter the table like this: if I choose december 2021 I wanto it to show all the sprints that started or fini...
Need Help
  • bcdobbs's avatar
    bcdobbs
    4 years ago

    Hi Pedro have a look at this demo.

     

    I used a disconnected date table.

     

    Calendar = 
ADDCOLUMNS (
    CALENDAR ( DATE (2020, 1, 1), DATE (2022, 12, 31) ),
    "Month Year", DATE ( YEAR([Date]), MONTH([Date]),1) //Format as MMMM YYYY
)

    Set both of them as Date type and then set format of Month Year to MMMM YYYY

     

    Created a measure in the Sprint table:

    Show Row = 

VAR DatesInContext = 
    VALUES ( 'Calendar'[Date] )

VAR StartDates = 
    TREATAS ( 
        VALUES ( Sprint[Start date] ),
        'Calendar'[Date] 
    )

VAR EndDates = 
    TREATAS ( 
        VALUES ( Sprint[End Date] ), 
        'Calendar'[Date] 
    )

VAR AllDates = 
    UNION (StartDates, EndDates)

VAR CountDateIntersect = 
    COUNTROWS(
        INTERSECT ( DatesInContext, AllDates )
    )

VAR Result = 
    IF ( CountDateIntersect > 0, 1, 0 )

RETURN Result

     

    Created a table visual and added the measure as a visual level filter:

     

     

sizi's avatar
sizi
Helper II
2 years ago

I have similar requirement however my data contains 8 date columns. I need to date filter to records based on date selection and show records pertaining to filtered date from all date columns . Note all dates will be there. Some dates available some blank. Whichever date is available we need to pull records. 

data columns are as below as I am not able to paste the data as it's giving error.

 

Task Name , Assigned date , Planned Start date, Planned end date, Actual start date, Actual end date , Initial Approval date , Technical Approval date , IT clearance date , Final Sign off date. 

If I select last 1month , all the records that fall under one month irrespective of date columns should show. Kindly help. 

I tried calendar table and connect the date to planned start date. However when I filter it only considers the records within planned start date selection and not show records that has data within the date selected. 

Any support is appreciated.

 

Thanks in advance.