How to show all dates between to date columns

[GilesWalker]

Hi Everyone,

I have a table in SQL which contains a unique ID number (sr_number) a date to and a date from plus other information. The problem I am trying to solve is I need to be able to see what days the unique ID number is valid for. I have a calender table in the model (DateKey). I want to have a graph with DateKey[Date] on the x-axis and then the values in the graph will count of all valid/open unique ID's.

The only way I can visualise it is that I need to create a seperate table with all the dates for each unique ID. Tried a bunch of different ways but keep hitting a dead end.

Hope some one ca help.

Here is a picture of the data:

If date_to is empty it means it is open and a close date has not been decided yet.

Thanks,

Giles

[Phil_Seamark · Accepted solution]

Hi @GilesWalker

You could try creating a calculated table in DAX.  This might be close.  Just rename my 'Table5' to whatever your base table is called.

Daily Table = 
FILTER(
                CROSSJOIN(
                    'Table5',
                    CALENDARAUTO()
                    ),
                    
                    'Table5'[date_from]<=[Date] 
                    && [Date]  <= VAR 
                                      EndDate = 'Table5'[date_to]
                                  RETURN 
                                      IF(EndDate = BLANK(),Today(),EndDate)
                    )