Forum Discussion
Solved
DEALING WITH START AND END DATES
Hello guys, I have a start and end date columns below. Account ID Start Date End Date 1234 1/25/2022 3/25/2022 5678 2/11/2023 9/10/2...
Set up a date table, marked as a date table. There's plenty of videos and articles about how to do that.
Don't create any relationships from the date table to your fact table.
Create a measure like
Account is visible =
VAR MaxDate =
MAX ( 'Date'[Date] )
VAR StartDate =
SELECTEDVALUE ( 'Table'[Start date] )
VAR EndDate =
SELECTEDVALUE ( 'Table'[End date] )
VAR Result =
IF ( StartDate <= MaxDate && ( ISBLANK ( EndDate ) || EndDate >= MaxDate ), 1 )
RETURN
Result
Add this as a filter to a table / matrix visual and it will show those accounts active on the selected date. Use columns from the date table in the slicer.
Hey John,
Thank you so much. this solved it. this actually created a flag for active or inactive on a selected date. what i want next is the do a count of the flags where it is 1 or 0. but i cant.
