Calculating Days in Status and Average Days in Status

[RDA_PowerBI]

Hi All, 

I have been struggling with the best approach to tackle this for a few days. I have a large dataset of 50 million rows that captures the Status of different Serial No's on a daily basis (70,000 rows for each Date of Record). 

I am after two things:

• Days in Current Status for each Serial No. 
• Avg Days by Status (Has to consider each event) 

A calculated column doesn't seem to work due to size. Here's a small sample: 

https://drive.google.com/open?id=1nIXJLii6DZgluKK0PAljJzbYRZ_I98jk 

Appreciate any help I can get! 

[Greg_Deckler]

FYI, I didn't have access to the file...

[Greg_Deckler]

The first problems seems like an EARLIER problem to me...See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

The second problem seems like a straigh-up SUMMARIZE.

I'll see if I can take a look at the data.

[RDA_PowerBI]

Hi Greg, 

I really appreciate your time! Your post on MTBF was very insightful and it makes me very excited about what I can do with the data I have. We have several more statuses (and attritubes) that can really help us narrow down problematic equipment. 

I was on a similiar track to your "uptime" caclulated column but it wasn't given me the expected results:  

Days = 
VAR PR =
    TOPN (
        1,
        FILTER (
            'Fixed Asset History',
            [Serial No] = EARLIER ( [Serial No] )
                && [Status] = EARLIER ( [Status] )
                && [Date of Record] < EARLIER ( [Date of Record] )
        ),
        [Date of Record], ASC
    )
VAR Pdate =
    MINX ( PR, [Date of Record] )
RETURN
    IF ( ISBLANK ( PDate ), 1, DATEDIFF ( Pdate, [Date of Record], DAY ) + 1 )
 

It was close, but Serial No "A" should reset to 1 on 01/05/18 and it doesn't. Seems like I'd be better off with a start and end time for each event but not sure. It also wouldn't calculate on my larger data set. 

The other measures you included in the post will be great for our REPAIR statuses. If I can get the days to calculate correctly, that would get me that much closer. 

Again, appreciate your time! 

https://drive.google.com/open?id=1nIXJLii6DZgluKK0PAljJzbYRZ_I98jk