topic Re: Help Calculating T=0, T=1, T=2... in Desktop
https://community.powerbi.com/t5/Desktop/Help-Calculating-T-0-T-1-T-2/m-p/1238030#M547955
<P>Hi <LI-USER uid="200525"></LI-USER> ,</P>
<P> </P>
<P>Just a supplement to the case of T=0:</P>
<LI-CODE lang="markup">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
)</LI-CODE>
<P> </P>Tue, 21 Jul 2020 09:50:36 GMTv-eachen-msft2020-07-21T09:50:36ZHelp Calculating T=0, T=1, T=2...
https://community.powerbi.com/t5/Desktop/Help-Calculating-T-0-T-1-T-2/m-p/1236064#M547383
<P>Hi everyone,</P><P> </P><P>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.</P><P> </P><P><STRONG>Scenario:</STRONG></P><P>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.</P><P> </P><P><STRONG>Chart example:</STRONG></P><P><span class="lia-inline-image-display-wrapper lia-image-align-left" image-alt="Capture.JPG" style="width: 400px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/315568i45E42697879D64D8/image-size/medium?v=v2&px=400" role="button" title="Capture.JPG" alt="Capture.JPG" /></span></P><P> </P><P> </P><P> </P><P> </P><P> </P><P> </P><P> </P><P> </P><P> </P><P>NOTE: T=0 is given to me as a fixed dataset.</P><P> </P><P><STRONG>Here is a table example of the data:</STRONG></P><TABLE border="1"><TBODY><TR><TD>Serial Number</TD><TD>Failure Date</TD><TD>Failure Month</TD><TD>Count failures per month</TD><TD>T=0</TD><TD>T=X<BR /><FONT color="#FF0000">(not sure how to calculate this)</FONT></TD></TR><TR><TD>12345</TD><TD>02-Dec-2017</TD><TD>Dec-2017</TD><TD>1</TD><TD>Dec-2016</TD><TD>T=1</TD></TR><TR><TD>12345</TD><TD>18-Sep-2019</TD><TD>Sep-2019</TD><TD>2</TD><TD>Dec-2016</TD><TD>T=2</TD></TR><TR><TD>1378Gb49</TD><TD>09-Sep-2019</TD><TD>Sep-2019</TD><TD>2</TD><TD>Jul-2017</TD><TD>T=1</TD></TR><TR><TD>2894GP20</TD><TD>27-Aug-2018</TD><TD>Aug-2018</TD><TD>1</TD><TD>Apr-2018</TD><TD>T=1</TD></TR><TR><TD>12345</TD><TD>26-Feb-2020</TD><TD>Feb-2020</TD><TD>1</TD><TD>Dec-2016</TD><TD>T=3</TD></TR></TBODY></TABLE><P> </P><P>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.</P><P> </P><P>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.</P><P> </P><P>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.</P><P>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.</P><P> </P><P>Thank you in advance!</P>Mon, 20 Jul 2020 19:39:35 GMThttps://community.powerbi.com/t5/Desktop/Help-Calculating-T-0-T-1-T-2/m-p/1236064#M547383Poweredrodrigo2020-07-20T19:39:35ZRe: Help Calculating T=0, T=1, T=2...
https://community.powerbi.com/t5/Desktop/Help-Calculating-T-0-T-1-T-2/m-p/1236186#M547421
<P>Hi <LI-USER uid="200525"></LI-USER> </P>
<P>it looks like a good task for RANKX column:</P>
<LI-CODE lang="markup">T =
var _Rank =
RANKX(
FILTER(
'Table',
'Table'[Serial Number]=EARLIER('Table'[Serial Number])
),
'Table'[Failure Date], , ASC
)
RETURN
"T = " & _Rank </LI-CODE>Mon, 20 Jul 2020 19:22:20 GMThttps://community.powerbi.com/t5/Desktop/Help-Calculating-T-0-T-1-T-2/m-p/1236186#M547421az382020-07-20T19:22:20ZRe: Help Calculating T=0, T=1, T=2...
https://community.powerbi.com/t5/Desktop/Help-Calculating-T-0-T-1-T-2/m-p/1238030#M547955
<P>Hi <LI-USER uid="200525"></LI-USER> ,</P>
<P> </P>
<P>Just a supplement to the case of T=0:</P>
<LI-CODE lang="markup">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
)</LI-CODE>
<P> </P>Tue, 21 Jul 2020 09:50:36 GMThttps://community.powerbi.com/t5/Desktop/Help-Calculating-T-0-T-1-T-2/m-p/1238030#M547955v-eachen-msft2020-07-21T09:50:36ZRe: Help Calculating T=0, T=1, T=2...
https://community.powerbi.com/t5/Desktop/Help-Calculating-T-0-T-1-T-2/m-p/1239230#M548337
<P>Hi <LI-USER uid="82403"></LI-USER> ,</P><P> </P><P>My preliminary tests have been successful with your formula.</P><P>I'll keep working on it but I think that you got it.</P><P> </P><P>Thank you very much for the great solution.</P><P>Cheers!</P>Tue, 21 Jul 2020 17:35:39 GMThttps://community.powerbi.com/t5/Desktop/Help-Calculating-T-0-T-1-T-2/m-p/1239230#M548337Poweredrodrigo2020-07-21T17:35:39Z