DEALING WITH START AND END DATES

[wemsomba10]

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/2023

  9263                 2/15/2023    

   8956               4/03/2024      5/11/2024

I need a date column that will have all dates so i can be able to say "here are the accounts that wee active on this date. the issue is i will reve wrong data if i just join the calendar table to either start date or end date on the fact table.  I tried a custom column in power query but it is not working due to null values in the end date column.  Any suggestions please?

[Ashish_Excel · Accepted solution]

Hi,

Download the PBI file from here.

Hope this helps.

[wemsomba10]

the second part of the filter should be a combination of and and or.  && data[endate] >Max(calendar[date] || ISBLANK(data[enddate]). I am unable to use a third criteria ina filter function.

[Ashish_Excel]

Study my solution carefully.  You will not need that second condition.

[wemsomba10]

hey Ashish. thank you so much. this one works better. thank you

[Anonymous]

@wemsomba10 

I think you could also try this. Similar to what Johnt75 said, create a Calendar table. Dont relate the tables.

The difference in this approach is that we create a measure that explicitly shows whether the account is "Active" or "Inactive".

1. In your data table, replace nulls in the End date with 31/12/9999. (You mentioned nulls in your End date, so use this to resolve that)

2. Add a filter on the Date field from the Calendar table. (the drop down in the snapshot below)

3. Write this measure and add to your table visual.

4. if you only want your output table to show Actives...then filter the table on Account Status "is Active"

The date filter could become cumbersome if you have a huge date range, but just another idea for you.

[johnt75]

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.

[wemsomba10]

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.