Forum Discussion
Calculate First time right values for multiple weeks
Hello,
I am new at the topic to create my own tables. So I stock quite early in the process. So, I wanted to explain the whole problem. Not that I fail due to the wrong starting idea.
I have two tables with tickets of a bug tracking system. One table ("Done_tickets") contains all tickets with the "Updated" date which have been closed. The second table ("Reopened_tickes") contains the amount of tickets which have been reopened in the same time range as well with the colunm "Updated".
Both table have more colunms e.g. "Issue id" or "Issue Type".
I want to craete a table which has 12 rows with the colunms "Date" and "FTR100".
The "Date" should start from today and then go into the past in 1 week steps 12 times.
The "FTR100" schould be computed:
- start-date is the value of "Date"
- end-date should be taken from the last entry of 100 tickets of "Done_tickets" beginning with the start date and sorted by date
- the value of "FTR100" is then 100 - NumberOfRows of "Reopened_tickes" where the "Updated" date is in the limit of start-date and end-date
Previously I calculated the "FTR100" part in SQL and requested our old Bug tracking system with multiple queries and combined the tables later. But now I can only compute the tickets with certain status changes and have to move the calculation into Power BI.
I started to calculate the end-date from today on but got stock due to I can't refer to colunms of VAR tables:
Nico
- Anonymous5 years ago
I found my way now. I just needed to add or substruct days from TODAY this was easy.
The rest was based of the first answer. And at the end a lot of copy and edit.
I don't know if it would be possible with a function:FTR100 = var start_date = TODAY() + 1 VAR temp_done = SELECTCOLUMNS ( Done_tickets, "ID", [Issue id], "Date", [Updated], "Rank", COUNTROWS ( FILTER ( FILTER(Done_tickets, [Updated].[Date] <= start_date), [Updated] >= EARLIER ( Done_tickets[Updated] ) ) ) + 1 ) VAR end_date = MINX ( FILTER ( temp_done, [Rank] <= 100 ), [Date] ) VAR temp_reopened = SELECTCOLUMNS ( Reopened_tickets, "ID", [Issue id], "Date", [Updated], "Rank", COUNTROWS ( FILTER ( FILTER(Reopened_tickets, [Updated].[Date] <= start_date), [Updated] >= EARLIER ( Reopened_tickets[Updated] ) ) ) + 1 ) var start_date_7 = TODAY() - 6 VAR temp_done_7 = SELECTCOLUMNS ( Done_tickets, "ID", [Issue id], "Date", [Updated], "Rank", COUNTROWS ( FILTER ( FILTER(Done_tickets, [Updated].[Date] <= start_date_7), [Updated] >= EARLIER ( Done_tickets[Updated] ) ) ) + 1 ) VAR end_date_7 = MINX ( FILTER ( temp_done_7, [Rank] <= 100 ), [Date] ) VAR temp_reopened_7 = SELECTCOLUMNS ( Reopened_tickets, "ID", [Issue id], "Date", [Updated], "Rank", COUNTROWS ( FILTER ( FILTER(Reopened_tickets, [Updated].[Date] <= start_date_7), [Updated] >= EARLIER ( Reopened_tickets[Updated] ) ) ) + 1 ) RETURN { (start_date, end_date, 100 - COUNTROWS( FILTER(temp_reopened, [Date] >= end_date && [Date] <= start_date))), (start_date_7, end_date_7, 100 - COUNTROWS( FILTER(temp_reopened_7, [Date] >= end_date_7 && [Date] <= start_date_7))), }
8 Replies
- AnonymousNot applicable
Hi Anonymous,
Can you please share some dummy data with a similar data structure and expected results? It should help us clarify your scenario and test to coding formula.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
- AnonymousNot applicable
Dummy data above.
- AnonymousNot applicable
HI Anonymous,
You can try to use the following formula if it is suitable for your requirement.
Calculte column= VAR temp = SELECTCOLUMNS ( Done_tickets, "ID", [Issue id], "Date", [Updated], "Rank", COUNTROWS ( FILTER ( Done_tickets, [Issue id] >= EARLIER ( Done_tickets[Issue id] ) ) ) + 1 ) RETURN MINX ( FILTER ( temp, [Rank] <= 100 ), [Updated] )Regards,
Xiaoxin Sheng
- AnonymousNot applicable
Thanks for response. Here some data.
"Done_tickets" fill the gap with Excel (one ticket per day)
Issue id Updated 1 01.01.2021 10:00 2 02.01.2021 10:00 3 03.01.2021 10:00 4 04.01.2021 10:00 ... ... 183 02.07.2021 10:00 184 03.07.2021 10:00 185 04.07.2021 10:00 186 05.07.2021 10:00
"Reopened_tickets" (complete)
Issue id Updated 8 08.01.2021 10:00 23 23.01.2021 10:00 29 29.01.2021 10:00 32 01.02.2021 10:00 44 13.02.2021 10:00 49 18.02.2021 10:00 59 28.02.2021 10:00 65 06.03.2021 10:00 73 14.03.2021 10:00 79 20.03.2021 10:00 86 27.03.2021 10:00 88 29.03.2021 10:00 90 31.03.2021 10:00 99 09.04.2021 10:00 100 10.04.2021 10:00 106 16.04.2021 10:00 109 19.04.2021 10:00 115 25.04.2021 10:00 123 03.05.2021 10:00 127 07.05.2021 10:00 130 10.05.2021 10:00 139 19.05.2021 10:00 144 24.05.2021 10:00 153 02.06.2021 10:00 161 10.06.2021 10:00 174 23.06.2021 10:00 178 27.06.2021 10:00 181 30.06.2021 10:00 183 02.07.2021 10:00 185 04.07.2021 10:00 FTR100 results
Date Test passed 05.07.2021 81 28.06.2021 83 21.06.2021 83 14.06.2021 83 07.06.2021 82 31.05.2021 83 24.05.2021 82 17.05.2021 87 10.05.2021 86 03.05.2021 83 26.04.2021 83 19.04.2021 84