Forum Discussion

ILAN102010SH's avatar
ILAN102010SH
Frequent Visitor
9 years ago
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...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    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's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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's avatar
ILAN102010SH
Frequent Visitor
9 years ago
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