Forum Discussion
Creating historical table of dates
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
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] )
7 Replies
- Phil_Seamark
Microsoft Employee
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] )- 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 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! :)
- Greg_Deckler
Community Champion
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.
