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.
Hi All,
I am trying to create a calculated table that lists all tickets and a record for each day that it was opened based on the Start Date and End Date columns. If the End Date is blank (i.e. the ticket is still open), I want it to list all dates from the start date to today's date.
For example, today's date is 5/15/2018. I would want the data from this table:
TicketStart DateEnd Date
1 | 5/11/2018 | 5/14/2018 |
2 | 5/11/2018 |
To calculate into this:
TicketDate
1 | 5/11/2018 |
1 | 5/12/2018 |
1 | 5/13/2018 |
1 | 5/14/2018 |
2 | 5/11/2018 |
2 | 5/12/2018 |
2 | 5/13/2018 |
2 | 5/14/2018 |
2 | 5/15/2018 |
I can't quite get there. Any ideas?
Solved! Go to Solution.
FYI - I pieced together some google searches and was able to do what I needed in the query editor:
FYI - I pieced together some google searches and was able to do what I needed in the query editor:
Hi @Anonymous
I think this calculated table gets close. I have attached a PBIX file.
Table 2 = SELECTCOLUMNS( GENERATE( 'Table1', FILTER( CALENDAR(MIN('Table1'[StartDate]),MAX('Table1'[DateEnd])) ,[Date]>=[StartDate] && [Date] <= IF(NOT IsBlank([DateEnd]),[DateEnd],MAX('Table1'[DateEnd]) ) ) ),"ID",[ID],"TicketDate",[Date])
@Phil_Seamark is there a way to do this but look at months so rather looking at every day date look at every month date?
if there a way to do this but look at months so rather looking at every day date look at every month date?
Thanks Phil, however it didn't list today's date for the ID 2 record, which is the one that didn't have an end date. I was able to piece together a solution from some googling I did and posted it as a solution for reference.
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |