Solved: Calculate First time right values for multiple wee...

Anonymous · ‎07-01-2021

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 · ‎07-08-2021

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))),
   }

View solution in original post

Anonymous · ‎07-05-2021

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

v-shex-msft · ‎07-04-2021

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Anonymous · ‎07-06-2021

Dummy data above.

v-shex-msft · ‎07-07-2021

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Anonymous · ‎07-07-2021

Hi Xiaoxin,
this looks promissing but I am to unexperianced to get this into a table. Can you change it, so, I get just one entry into a table.
There is a lot of stuff I don't know in Power BI.
Regards,
Nico Hollmann

Anonymous · ‎07-07-2021

I found it.

Anonymous · ‎07-07-2021

I have almost the first line. But I realized when I take TODAY then I miss the tickets created today.

So I need tomorrow (compute with dates) but I can't find the right function which adds/substruct days from a datetime (not column) and returns only a datetime

Thats my current state.

Tabelle = 
var start_date = TODAY()
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].[Date],
        "Rank",
            COUNTROWS (
                FILTER ( FILTER(Reopened_tickets, [Updated].[Date] <= start_date), [Updated] >= EARLIER ( Reopened_tickets[Updated] ) )
            ) + 1
    )
RETURN
    {(start_date, end_date, 100 - COUNTROWS( FILTER(temp_reopened, [Date] >= end_date || [Date] <= start_date)))}

Anonymous · ‎07-08-2021

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))),
   }

Calculate First time right values for multiple weeks

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024

How to Get Your Question Answered Quickly