cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Poweredrodrigo
Frequent Visitor

Help Calculating T=0, T=1, T=2...

Hi everyone,

 

Need some assistance calculating T values. I thought about using Exponential Regression but maybe that's not the right way. You guys may have a simpler solution.

 

Scenario:

I have a list of different hadware equipment that have been failling over time. I'm trying to plot these failures based on their actual date but converted to T=X instead of using the date itself.

 

Chart example:

Capture.JPG

 

 

 

 

 

 

 

 

 

NOTE: T=0 is given to me as a fixed dataset.

 

Here is a table example of the data:

Serial NumberFailure DateFailure MonthCount failures per monthT=0T=X
(not sure how to calculate this)
1234502-Dec-2017Dec-20171Dec-2016T=1
1234518-Sep-2019Sep-20192Dec-2016T=2
1378Gb4909-Sep-2019Sep-20192Jul-2017T=1
2894GP2027-Aug-2018Aug-20181Apr-2018T=1
1234526-Feb-2020Feb-20201Dec-2016T=3

 

You will notice that from the table above, the serial number 12345 failed 3 times in a period of 3 years (T=0 being Dec-2016). Therefore each failure gains a +1 to T=X. However, the T=X calculation should always start from the base T=0 date.

 

NOTE: Each individual serial number will have their own set of T=X. I have a lot of different serial numbers, the plan is to group all failures by month (independent of serial number). The serial number is only used to properly calculate T=X.

 

I'm preparing this in Power BI, I'm assuming I will have to use Power Query due to the complexity. I'm not the greatest mathematician out there so I'm probably stuck on something simple to resolve.

Please let me know if this is something you guys can assist. Also, let me know if you require more information and I'll add to this post.

 

Thank you in advance!

1 ACCEPTED SOLUTION
az38
Super User
Super User

Hi @Poweredrodrigo 

it looks like a good task for RANKX column:

T = 
var _Rank = 
    RANKX(
        FILTER(
                'Table',
                'Table'[Serial Number]=EARLIER('Table'[Serial Number])
                ),
        'Table'[Failure Date], , ASC
        )

RETURN

"T = " & _Rank 

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

3 REPLIES 3
v-eachen-msft
Community Support
Community Support

Hi @Poweredrodrigo ,

 

Just a supplement to the case of T=0:

T =
VAR _Rank =
    RANKX (
        FILTER ( 'Table', 'Table'[Serial Number] = EARLIER ( 'Table'[Serial Number] ) ),
        'Table'[Failure Date],
        ,
        ASC
    )
RETURN
    IF (
        FORMAT ( 'Table'[T=0], "yyyymm" )
            = FORMAT (
                CALCULATE (
                    MIN ( 'Table'[Failure Month] ),
                    ALLEXCEPT ( 'Table', 'Table'[Serial Number] )
                ),
                "yyyymm"
            ),
        "T = " & _Rank - 1,
        "T = " & _Rank
    )

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
az38
Super User
Super User

Hi @Poweredrodrigo 

it looks like a good task for RANKX column:

T = 
var _Rank = 
    RANKX(
        FILTER(
                'Table',
                'Table'[Serial Number]=EARLIER('Table'[Serial Number])
                ),
        'Table'[Failure Date], , ASC
        )

RETURN

"T = " & _Rank 

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

Hi @az38 ,

 

My preliminary tests have been successful with your formula.

I'll keep working on it but I think that you got it.

 

Thank you very much for the great solution.

Cheers!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.