Forum Discussion
Solved
Creating historical table of dates
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...
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] )
Phil_Seamark
Microsoft Employee
Microsoft EmployeeHi 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! :)
