Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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:

Tabelle 2 =
VAR temp_Updated = SELECTCOLUMNS(Done_tickets, "Date", [Updated].[Date])
VAR temp_topN = TOPN(100, temp_Updated, [Date])
RETURN
MIN(temp_topN[Updated])
 
I hope someone can help me. At least to come a bit further.
 
Thanks
Nico
  • Anonymous's avatar
    Anonymous
    5 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

  • Anonymous's avatar
    Anonymous
    Not 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

    • Anonymous's avatar
      Anonymous
      Not applicable

      Dummy data above.

      • Anonymous's avatar
        Anonymous
        Not 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

  • Anonymous's avatar
    Anonymous
    Not applicable

    Thanks for response. Here some data.

     

    "Done_tickets" fill the gap with Excel (one ticket per day)

    Issue idUpdated
    101.01.2021 10:00
    202.01.2021 10:00
    303.01.2021 10:00
    404.01.2021 10:00
    ......
    18302.07.2021 10:00
    18403.07.2021 10:00
    18504.07.2021 10:00
    186

    05.07.2021 10:00

     

    "Reopened_tickets" (complete)

    Issue idUpdated
    808.01.2021 10:00
    2323.01.2021 10:00
    2929.01.2021 10:00
    3201.02.2021 10:00
    4413.02.2021 10:00
    4918.02.2021 10:00
    5928.02.2021 10:00
    6506.03.2021 10:00
    7314.03.2021 10:00
    7920.03.2021 10:00
    8627.03.2021 10:00
    8829.03.2021 10:00
    9031.03.2021 10:00
    9909.04.2021 10:00
    10010.04.2021 10:00
    10616.04.2021 10:00
    10919.04.2021 10:00
    11525.04.2021 10:00
    12303.05.2021 10:00
    12707.05.2021 10:00
    13010.05.2021 10:00
    13919.05.2021 10:00
    14424.05.2021 10:00
    15302.06.2021 10:00
    16110.06.2021 10:00
    17423.06.2021 10:00
    17827.06.2021 10:00
    18130.06.2021 10:00
    18302.07.2021 10:00
    18504.07.2021 10:00

     

    FTR100 results

    DateTest passed
    05.07.202181
    28.06.202183
    21.06.202183
    14.06.202183
    07.06.202182
    31.05.202183
    24.05.202182
    17.05.202187
    10.05.202186
    03.05.202183
    26.04.202183
    19.04.202184