Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Solved! Go to Solution.
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] )
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!
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])
Nice mod! 🙂
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |