Calculated column to count repeated activities?

[brokencornets]

Hi

I have a table showing breakdowns for a number of assets including the date raised and date completed. What I'm trying to do is track how many times an asset is failing after being fixed within the last 30 days.

In Exce lI'd use something along the lines of: 

=IF(COUNTIFS(A:A,A2,G:G,"<"&F2,G:G,">="&F2-30)>0,"Y","N")

where column A is asset name, G is completed date and F is raised date.

Not sure if it's possible to do this with a measure, I was imagining more a calculated column but I'm a bit stuck on how to approach it and Google hasn't offered much assistance this time.

This was a bit of a hail mary attempt and a huge fail, but it shows the column names, etc. at least so might be useful:

Repeat Fault? =

CALCULATE (

COUNTROWS ( 'Jobs' ),

FILTER (

'Jobs',

'Jobs'[Asset] = 'Jobs'[Asset]

&& 'Jobs'[Date Reported] < 'Jobs'[Date Reported]

&& 'Jobs'[Date Reported]>'Jobs'[Date Reported]-30

)

)

[v-xiaotang]

Hi @brokencornets 

try this,

Repeat Fault = 
CALCULATE (
COUNTROWS ( 'Jobs' ),
FILTER (
ALL('Jobs'),
'Jobs'[Asset] = MIN('Jobs'[Asset])
&& 'Jobs'[Date Reported] < MIN('Jobs'[Date Reported])
&& 'Jobs'[Date Reported]>MIN('Jobs'[Date Reported])-30
)
)

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[brokencornets]

Apologies for the slow reply, I haven't been able to work on this report this week. The solution works fantastically well on a small sample but seems to really struggle performance-wise when I add it to the main report. Wondering if it might be better (if it's possible?) to do something in the Power Query side of things instead?

[v-xiaotang · Accepted solution]

Hi @brokencornets 

M code, copy it into Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY4xEsAgCAT/Yq2FKFH61PmAY5VJnS7vDzBKYsMNt3DQmovOuwhJajjuhzWpdt8ccIeIPxaNyQYVnRiMjOUvs8zFYlTyqJKYdVAAoxu3OcsMpEnRaLGPIOzXyap2tYOLTRoNi91f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Job Number" = _t, Asset = _t, #"Date Reported" = _t, #"Date Completed" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Job Number", Int64.Type}, {"Asset", Int64.Type}, {"Date Reported", type date}, {"Date Completed", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Count", each let asset = [Asset], dr = [Date Reported]
in 
Table.RowCount(
    Table.SelectRows(
        #"Changed Type",
        each [Asset] = asset and Number.From([Date Reported]) > Number.From(dr) -30 and [Date Completed] < dr
    )
))
in
    #"Added Custom"

What's your datasource? And what's the connection mode? In some cases, it depends on the speed of your data source, or the computing speed of your computer.

Here is an article for your reference, link. Kindly note: 

(1)

(2)

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. Please show the expected outcome.

[brokencornets]

So the data is structured as per the first 4 columns below below, and for each row I'd like to check if there has been another job for the same asset that was closed within the previous 30 days.

In the below, line 4 is a 'repeat fault' as asset 123 was last fixed on 3 Dec and broke down again 14 days later, same with line 8 - so in total I'd want an answer of 2 for total repeat faults. Line 7 would NOT be a repeat fault because there are 31 days between each job.

I was thinking the best approach would be a calculated column called Repeat Fault which is a simple YorN column? (5th column) Or even better, maybe this column would have the previous job number in (6th column)

Job Number	Asset	Date Reported	Date Completed	Repeat Fault	Repeat Fault - Previous Job Number
1	123	01-Nov	03-Nov	N
2	555	01-Nov	01-Nov	N
3	972	05-Nov	09-Nov	N
4	123	17-Nov	17-Nov	Y	1
5	989	18-Nov	22-Nov	N
6	445	23-Nov	25-Nov	N
7	555	02-Dec		N
8	123	02-Dec		Y	4
9	222	02-Dec		N