Creating historical table of dates

[ILAN102010SH]

Hello, I have one table with columns ( user id, start date, end date)
Is it possible to make a new calender table that in it,
Will be the user id exactly in the range of dates between start date and end date?
How can I do it?
Your help will be very appreciated!
Best Regards,
Ilan-S

[Phil_Seamark · Accepted solution]

Hi @ILAN102010SH

You could try the following calculated table.  In my case I have a table called 'Table4' with UserID, Start Date and End Date

My New Table = SELECTCOLUMNS(
                    FILTER(
			         CROSSJOIN(CALENDARAUTO() , 'Table4') ,
				        [Date] >= 'Table4'[Start Date]
				    && [Date] <= 'Table4'[End Date]
		          ),
                  "UserID" ,'Table4'[UserID] ,
                  "Date" , [Date]
                )

[ILAN102010SH]

Hi @Phil_Seamark 

thank you very much!

I tried your solution and it's working good.

just one thing to say - in your solution you don't consider users with no end date,

so I added this expression to your formula.

&& [Date] <= 'Users'[end_date]
|| [Date] >= 'Users'[start_date]&& ISBLANK('Users'[end_date])

[Phil_Seamark]

Nice mod! 🙂

[ILAN102010SH]

Thank you very much @Phil_Seamark
I'm a new beginner in Power Bi.
Could you please send me a sample of pbix file.
Regards,
Ilan-S

[Phil_Seamark]

Hi @ILAN102010SH

Give this a crack

https://1drv.ms/u/s!AtDlC2rep7a-kHHzrvSHx2gFhl3m

[ILAN102010SH]

Thank you very much @Phil_Seamark.
I'll give it a try.
I let you know if it works.

[Greg_Deckler]

You can create a new table with the formula:

Table = CALENDARAUTO()

This creates a date table that autogenerates from the dates in your data model. Thus, if you created a data model with a single row from your table, you might get lucky.