Forum Discussion
pva
4 years agoFrequent Visitor
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...
- 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 ResultCreated a table visual and added the measure as a visual level filter:
bcdobbs
4 years agoCommunity Champion
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: