Consecutive Values

DAX DAX Calculations
1

Hi,

Dealing with a scenario where we have vehicles and their Operational Status, which is either Normal Operation or Not Operational.

Data is snapshotted TWICE every day so we have a running operational status. A Snapshot is taken at 08:00am and 16:00pm. For this calculation I only want to focus on the snapshot taken at 08:00am.

What I need to do is to classify the number of vehicles (assets) which are Not Operational and split this into Short Term and Long Term groups.

To be classed as Short Term Not Operational they need to be in a status of Not Operational at this current time for 9 days or Less.

To be Classed as Long Term Not Operational they need to be in a Status of Not Operational at this current time for 10 days or more.

Example data for Asset 1 - Asset has been Not Operational for 13 days so is counted as Long Term Not Operational as per below

image

Example data for Asset 2 - Asset has been Not Operational for 2 days so is counted as Short Term Not Operational

image

image

Example data for Asset 4 - Asset has been Not Operational for 18 days so is counted as Long Term Not Operational as per below

image

I had this solved in a previous thread but the requirements have changed slightly with the removal in the data of an IS CURRENT flag and this has been replaced with the Snapshot time (08:00am or 16:00).

I have tried to amend the DAX myself but I can’t seem to get it working properly. Any help, as always gratefully received. Link to previous thread at bottom of this post along with PBIX file.

Not Operational Assets - Long Term = 
VAR _vTable = 
ADDCOLUMNS(
    FILTER(
        SUMMARIZE(
            Assets , 
            Assets[Date] ,
            Assets[Asset ID] ,
            Assets[Asset Status] ,
            Assets[Snapshot Time] ) , 
        ( Assets[Asset Status] = "Normal Operation" &&
        Assets[Snapshot Time] = TIME( 8, 0, 0 ) ) ||
        ( Assets[Asset Status] = "Not Operational" &&
        Assets[Date] >= MAX( Assets[Date] ) - 10 &&
        Assets[Date] <= MAX( Assets[Date] ) ) ) , 
    
    "@Count_Rows" , 
    CALCULATE(
        COUNTROWS(
            FILTER(
                SUMMARIZE(
                    Assets , 
                    Assets[Date] ,
                    Assets[Asset ID] ,
                    Assets[Asset Status] ,
                    Assets[Snapshot Time] ) , 
                ( Assets[Asset Status] = "Normal Operation" &&
                Assets[Snapshot Time] = TIME( 8, 0, 0 ) ) ||
                ( Assets[Asset Status] = "Not Operational" &&
                Assets[Date] >= MAX( Assets[Date] ) - 10 &&
                Assets[Date] <= MAX( Assets[Date] ) ) ) ) , 
        ALLEXCEPT( Assets , Assets[Asset ID] ) ) , 

    "@Days_Diff" , 
    DATEDIFF(
        
        MINX( 
            CALCULATETABLE(
                FILTER(
                    SUMMARIZE(
                        Assets , 
                        Assets[Date] ,
                        Assets[Asset ID] ,
                        Assets[Asset Status] ,
                        Assets[Snapshot Time]  ) , 
                    ( Assets[Asset Status] = "Normal Operation" &&
                    Assets[Snapshot Time] = TIME( 8, 0, 0 ) ) ||
                    ( Assets[Asset Status] = "Not Operational" &&
                    Assets[Date] >= MAX( Assets[Date] ) - 10 &&
                    Assets[Date] <= MAX( Assets[Date] ) ) ) , 
            ALLEXCEPT( Assets , Assets[Asset ID] ) ) , 
        Assets[Date] ) , 

        MAXX(
            CALCULATETABLE(
                FILTER(
                    SUMMARIZE(
                        Assets , 
                        Assets[Date] ,
                        Assets[Asset ID] ,
                        Assets[Asset Status] ,
                       Assets[Snapshot Time]  ) , 
                    ( Assets[Asset Status] = "Normal Operation" &&
                    Assets[Snapshot Time] = TIME( 8, 0, 0 ) ) ||
                    ( Assets[Asset Status] = "Not Operational" &&
                    Assets[Date] >= MAX( Assets[Date] ) - 10 &&
                    Assets[Date] <= MAX( Assets[Date] ) ) ) , 
            ALLEXCEPT( Assets , Assets[Asset ID] ) ) , 
        Assets[Date] ) ,

        DAY ) + 1 )

VAR _fTable =
SELECTCOLUMNS(
    FILTER(
        ADDCOLUMNS(
            _vTable , 
            "@Status_Define" ,
            SWITCH( TRUE() , 
                [Asset Status] = "Not Operational" && [@Count_Rows] = [@Days_Diff] && [@Count_Rows] <= 9 , "Short Term Not Operational" , 
                [Asset Status] = "Not Operational" && [@Count_Rows] = [@Days_Diff] && [@Count_Rows] >= 10 , "Long Term Not Operational" , 
                [Asset Status] = "Not Operational" && [@Count_Rows] <> [@Days_Diff] && [@Count_Rows] <= 9 , "Short Term Not Operational" , 
                [Asset Status] = "Normal Operation" && [@Count_Rows] = [@Days_Diff] && [@Count_Rows] =1 , "Normal Operation" ) ) , 
            Assets[Date] = MAX( Assets[Date] ) &&
            [@Status_Define] = "Long Term Not Operational" ) , 
    "@Status_Date" , Assets[Date] , 
    "@Asset_ID" , Assets[Asset ID] , 
    "@Original_Status" , Assets[Asset Status] , 
    "@Is_Current_Flag" ,  Assets[Snapshot Time] ,
    "@Status_Define" , [@Status_Define] )

VAR _Results = 
COUNTROWS( _fTable )

RETURN
_Results + 0

Not Operational Assets - Short Term = 
VAR _vTable = 
ADDCOLUMNS(
    FILTER(
        SUMMARIZE(
            Assets , 
            Assets[Date] ,
            Assets[Asset ID] ,
            Assets[Asset Status] ,
            Assets[Snapshot Time] ) , 
        ( Assets[Asset Status] = "Operational" &&
        Assets[Snapshot Time] = TIME (8, 0, 0 )) ||
        ( Assets[Asset Status] = "Not Operational" &&
        Assets[Date] >= MAX( Assets[Date] ) - 10 &&
        Assets[Date] <= MAX( Assets[Date] ) ) ) , 
    
    "@Count_Rows" , 
    CALCULATE(
        COUNTROWS(
            FILTER(
                SUMMARIZE(
                    Assets , 
                    Assets[Date] ,
                    Assets[Asset ID] ,
                    Assets[Asset Status] ,
                    Assets[Snapshot Time] ) , 
                ( Assets[Asset Status] = "Operational" &&
                Assets[Snapshot Time] = TIME (8, 0, 0 )) ||
                ( Assets[Asset Status] = "Not Operational" &&
                Assets[Date] >= MAX( Assets[Date] ) - 10 &&
                Assets[Date] <= MAX( Assets[Date] ) ) ) ) , 
        ALLEXCEPT( Assets , Assets[Asset ID] ) ) , 

    "@Days_Diff" , 
    DATEDIFF(
        
        MINX( 
            CALCULATETABLE(
                FILTER(
                    SUMMARIZE(
                        Assets , 
                        Assets[Date] ,
                        Assets[Asset ID] ,
                        Assets[Asset Status] ,
                        Assets[Snapshot Time] ) , 
                    ( Assets[Asset Status] = "Operational" &&
                    Assets[Snapshot Time] = TIME (8, 0, 0 )) ||
                    ( Assets[Asset Status] = "Not Operational" &&
                    Assets[Date] >= MAX( Assets[Date] ) - 10 &&
                    Assets[Date] <= MAX( Assets[Date] ) ) ) , 
            ALLEXCEPT( Assets , Assets[Asset ID] ) ) , 
        Assets[Date] ) , 

        MAXX(
            CALCULATETABLE(
                FILTER(
                    SUMMARIZE(
                        Assets , 
                        Assets[Date] ,
                        Assets[Asset ID] ,
                        Assets[Asset Status] ,
                        Assets[Snapshot Time] ) , 
                    ( Assets[Asset Status] = "Operational" &&
                    Assets[Snapshot Time] = TIME (8, 0, 0 )) ||
                    ( Assets[Asset Status] = "Not Operational" &&
                    Assets[Date] >= MAX( Assets[Date] ) - 10 &&
                    Assets[Date] <= MAX( Assets[Date] ) ) ) , 
            ALLEXCEPT( Assets , Assets[Asset ID] ) ) , 
        Assets[Date] ) ,

        DAY ) + 1 )

VAR _fTable =
SELECTCOLUMNS(
    FILTER(
        ADDCOLUMNS(
            _vTable , 
            "@Status_Define" ,
            SWITCH( TRUE() , 
                [Asset Status] = "Not Operational" && [@Count_Rows] = [@Days_Diff] && [@Count_Rows] <= 9 , "Short Term Not Operational" , 
                [Asset Status] = "Not Operational" && [@Count_Rows] = [@Days_Diff] && [@Count_Rows] >= 10 , "Long Term Not Operational" , 
                [Asset Status] = "Not Operational" && [@Count_Rows] <> [@Days_Diff] && [@Count_Rows] <= 9 , "Short Term Not Operational" , 
                [Asset Status] = "Operational" && [@Count_Rows] = [@Days_Diff] && [@Count_Rows] =1 , "Operational" ) ) , 
            Assets[Date] = MAX( Assets[Date] ) &&
            [@Status_Define] = "Short Term Not Operational" ) , 
    "@Status_Date" , Assets[Date] , 
    "@Asset_ID" , Assets[Asset ID] , 
    "@Original_Status" , Assets[Asset Status] , 
    "@Is_Current_Flag" , Assets[Snapshot Time] ,
    "@Status_Define" , [@Status_Define] )

VAR _Results =
COUNTROWS( _fTable )

RETURN
_Results + 0

Count of Consecutive Values - DAX / DAX Calculations - Enterprise DNA Forum

Short Term vs Long Term Status - Harsh v1.1.pbix (92.5 KB)

2

Hello @DavieJoe,

Thank You for posting your query onto the Forum.

In this case, since “Is Current Flag” field is not available by default in the data. We can create that column virtually and then achieve those exact same results. Below are all the 5 DAX measures alongwith the screenshot of the final results provided for the reference -

Operational Assets = 
VAR _vTable = 
ADDCOLUMNS(
    FILTER(
        ADDCOLUMNS(
            CALCULATETABLE(
                SUMMARIZE(
                    StatusData , 
                    StatusData[Date] ,
                    StatusData[Asset ID] , 
                    StatusData[Asset Status] ) ,
                StatusData[Snapshot Time] <> TIME( 16 , 0 , 0 ) ) , 
            "@Is_Current_Flag" , 
            IF( StatusData[Date] = MAX( StatusData[Date] ) , 1 , 0 ) ) , 
        ( StatusData[Asset Status] = "Normal Operation" && 
        [@Is_Current_Flag] = 1 ) ||
        ( StatusData[Asset Status] = "Not Operational" && 
        StatusData[Date] >= MAX( StatusData[Date] ) - 10 &&
        StatusData[Date] <= MAX( StatusData[Date] ) ) ) , 
    
    "@Count_Rows" , 
    CALCULATE( 
        COUNTROWS(
            FILTER(
                ADDCOLUMNS(
                    CALCULATETABLE(
                        SUMMARIZE(
                            StatusData , 
                            StatusData[Date] ,
                            StatusData[Asset ID] , 
                            StatusData[Asset Status] ) ,
                        StatusData[Snapshot Time] <> TIME( 16 , 0 , 0 ) ) , 
                    "@Is_Current_Flag" , 
                    IF( StatusData[Date] = MAX( StatusData[Date] ) , 1 , 0 ) ) , 
                ( StatusData[Asset Status] = "Normal Operation" && 
                [@Is_Current_Flag] = 1 ) ||
                ( StatusData[Asset Status] = "Not Operational" && 
                StatusData[Date] >= MAX( StatusData[Date] ) - 10 &&
                StatusData[Date] <= MAX( StatusData[Date] ) ) ) ) , 
            ALLEXCEPT( StatusData , StatusData[Asset ID] ) ) , 

    "@Days_Difference" , 
    DATEDIFF(

        MINX(
            CALCULATETABLE(
                FILTER(
                    ADDCOLUMNS(
                        CALCULATETABLE(
                            SUMMARIZE(
                                StatusData , 
                                StatusData[Date] ,
                                StatusData[Asset ID] , 
                                StatusData[Asset Status] ) ,
                            StatusData[Snapshot Time] <> TIME( 16 , 0 , 0 ) ) , 
                        "@Is_Current_Flag" , 
                        IF( StatusData[Date] = MAX( StatusData[Date] ) , 1 , 0 ) ) , 
                    ( StatusData[Asset Status] = "Normal Operation" && 
                    [@Is_Current_Flag] = 1 ) ||
                    ( StatusData[Asset Status] = "Not Operational" && 
                    StatusData[Date] >= MAX( StatusData[Date] ) - 10 &&
                    StatusData[Date] <= MAX( StatusData[Date] ) ) ) , 
                ALLEXCEPT( StatusData , StatusData[Asset ID] ) ) , 
            StatusData[Date] ) ,

        MAXX(
            CALCULATETABLE(
                FILTER(
                    ADDCOLUMNS(
                        CALCULATETABLE(
                            SUMMARIZE(
                                StatusData , 
                                StatusData[Date] ,
                                StatusData[Asset ID] , 
                                StatusData[Asset Status] ) ,
                            StatusData[Snapshot Time] <> TIME( 16 , 0 , 0 ) ) , 
                        "@Is_Current_Flag" , 
                        IF( StatusData[Date] = MAX( StatusData[Date] ) , 1 , 0 ) ) , 
                    ( StatusData[Asset Status] = "Normal Operation" && 
                    [@Is_Current_Flag] = 1 ) ||
                    ( StatusData[Asset Status] = "Not Operational" && 
                    StatusData[Date] >= MAX( StatusData[Date] ) - 10 &&
                    StatusData[Date] <= MAX( StatusData[Date] ) ) ) , 
                ALLEXCEPT( StatusData , StatusData[Asset ID] ) ) , 
            StatusData[Date] ) ,

        DAY ) + 1 )

VAR _fTable = 
SELECTCOLUMNS(
    FILTER(
        ADDCOLUMNS(
            _vTable , 
            "@Status_Define" ,
            SWITCH( TRUE() , 
                [Asset Status] = "Not Operational" && [@Count_Rows] = [@Days_Difference] && [@Count_Rows] <= 9 , "Short Term Not Operational" , 
                [Asset Status] = "Not Operational" && [@Count_Rows] = [@Days_Difference] && [@Count_Rows] >= 10 , "Long Term Not Operational" , 
                [Asset Status] = "Not Operational" && [@Count_Rows] <> [@Days_Difference] && [@Count_Rows] <= 9 , "Short Term Not Operational" , 
                [Asset Status] = "Normal Operation" && [@Count_Rows] = [@Days_Difference] && [@Count_Rows] =1 , "Normal Operation" ) ) , 
            StatusData[Date] = MAX( StatusData[Date] ) &&
            [@Status_Define] = "Normal Operation" ) , 
        "@Status_Date" , StatusData[Date] , 
        "@Asset_ID" , StatusData[Asset ID] , 
        "@Original_Status" , StatusData[Asset Status] , 
        "@Is_Current_Flag" , [@Is_Current_Flag] ,
        "@Status_Define" , [@Status_Define] )

VAR _Results = 
COUNTROWS( _fTable )

RETURN
_Results

Not Operational Assets = 
VAR _vTable = 
ADDCOLUMNS(
    FILTER(
        ADDCOLUMNS(
            CALCULATETABLE(
                SUMMARIZE(
                    StatusData , 
                    StatusData[Date] ,
                    StatusData[Asset ID] , 
                    StatusData[Asset Status] ) ,
                StatusData[Snapshot Time] <> TIME( 16 , 0 , 0 ) ) , 
            "@Is_Current_Flag" , 
            IF( StatusData[Date] = MAX( StatusData[Date] ) , 1 , 0 ) ) , 
        ( StatusData[Asset Status] = "Normal Operation" && 
        [@Is_Current_Flag] = 1 ) ||
        ( StatusData[Asset Status] = "Not Operational" && 
        StatusData[Date] >= MAX( StatusData[Date] ) - 10 &&
        StatusData[Date] <= MAX( StatusData[Date] ) ) ) , 
    
    "@Count_Rows" , 
    CALCULATE( 
        COUNTROWS(
            FILTER(
                ADDCOLUMNS(
                    CALCULATETABLE(
                        SUMMARIZE(
                            StatusData , 
                            StatusData[Date] ,
                            StatusData[Asset ID] , 
                            StatusData[Asset Status] ) ,
                        StatusData[Snapshot Time] <> TIME( 16 , 0 , 0 ) ) , 
                    "@Is_Current_Flag" , 
                    IF( StatusData[Date] = MAX( StatusData[Date] ) , 1 , 0 ) ) , 
                ( StatusData[Asset Status] = "Normal Operation" && 
                [@Is_Current_Flag] = 1 ) ||
                ( StatusData[Asset Status] = "Not Operational" && 
                StatusData[Date] >= MAX( StatusData[Date] ) - 10 &&
                StatusData[Date] <= MAX( StatusData[Date] ) ) ) ) , 
            ALLEXCEPT( StatusData , StatusData[Asset ID] ) ) , 

    "@Days_Difference" , 
    DATEDIFF(

        MINX(
            CALCULATETABLE(
                FILTER(
                    ADDCOLUMNS(
                        CALCULATETABLE(
                            SUMMARIZE(
                                StatusData , 
                                StatusData[Date] ,
                                StatusData[Asset ID] , 
                                StatusData[Asset Status] ) ,
                            StatusData[Snapshot Time] <> TIME( 16 , 0 , 0 ) ) , 
                        "@Is_Current_Flag" , 
                        IF( StatusData[Date] = MAX( StatusData[Date] ) , 1 , 0 ) ) , 
                    ( StatusData[Asset Status] = "Normal Operation" && 
                    [@Is_Current_Flag] = 1 ) ||
                    ( StatusData[Asset Status] = "Not Operational" && 
                    StatusData[Date] >= MAX( StatusData[Date] ) - 10 &&
                    StatusData[Date] <= MAX( StatusData[Date] ) ) ) , 
                ALLEXCEPT( StatusData , StatusData[Asset ID] ) ) , 
            StatusData[Date] ) ,

        MAXX(
            CALCULATETABLE(
                FILTER(
                    ADDCOLUMNS(
                        CALCULATETABLE(
                            SUMMARIZE(
                                StatusData , 
                                StatusData[Date] ,
                                StatusData[Asset ID] , 
                                StatusData[Asset Status] ) ,
                            StatusData[Snapshot Time] <> TIME( 16 , 0 , 0 ) ) , 
                        "@Is_Current_Flag" , 
                        IF( StatusData[Date] = MAX( StatusData[Date] ) , 1 , 0 ) ) , 
                    ( StatusData[Asset Status] = "Normal Operation" && 
                    [@Is_Current_Flag] = 1 ) ||
                    ( StatusData[Asset Status] = "Not Operational" && 
                    StatusData[Date] >= MAX( StatusData[Date] ) - 10 &&
                    StatusData[Date] <= MAX( StatusData[Date] ) ) ) , 
                ALLEXCEPT( StatusData , StatusData[Asset ID] ) ) , 
            StatusData[Date] ) ,

        DAY ) + 1 )

VAR _fTable = 
SELECTCOLUMNS(
    FILTER(
        ADDCOLUMNS(
            _vTable , 
            "@Status_Define" ,
            SWITCH( TRUE() , 
                [Asset Status] = "Not Operational" && [@Count_Rows] = [@Days_Difference] && [@Count_Rows] <= 9 , "Short Term Not Operational" , 
                [Asset Status] = "Not Operational" && [@Count_Rows] = [@Days_Difference] && [@Count_Rows] >= 10 , "Long Term Not Operational" , 
                [Asset Status] = "Not Operational" && [@Count_Rows] <> [@Days_Difference] && [@Count_Rows] <= 9 , "Short Term Not Operational" , 
                [Asset Status] = "Normal Operation" && [@Count_Rows] = [@Days_Difference] && [@Count_Rows] =1 , "Normal Operation" ) ) , 
            StatusData[Date] = MAX( StatusData[Date] ) &&
            StatusData[Asset Status] = "Not Operational" ) , 
        "@Status_Date" , StatusData[Date] , 
        "@Asset_ID" , StatusData[Asset ID] , 
        "@Original_Status" , StatusData[Asset Status] , 
        "@Is_Current_Flag" , [@Is_Current_Flag] ,
        "@Status_Define" , [@Status_Define] )

VAR _Results = 
COUNTROWS( _fTable )

RETURN
_Results

Not Operational Assets - Short Term = 
VAR _vTable = 
ADDCOLUMNS(
    FILTER(
        ADDCOLUMNS(
            CALCULATETABLE(
                SUMMARIZE(
                    StatusData , 
                    StatusData[Date] ,
                    StatusData[Asset ID] , 
                    StatusData[Asset Status] ) ,
                StatusData[Snapshot Time] <> TIME( 16 , 0 , 0 ) ) , 
            "@Is_Current_Flag" , 
            IF( StatusData[Date] = MAX( StatusData[Date] ) , 1 , 0 ) ) , 
        ( StatusData[Asset Status] = "Normal Operation" && 
        [@Is_Current_Flag] = 1 ) ||
        ( StatusData[Asset Status] = "Not Operational" && 
        StatusData[Date] >= MAX( StatusData[Date] ) - 10 &&
        StatusData[Date] <= MAX( StatusData[Date] ) ) ) , 
    
    "@Count_Rows" , 
    CALCULATE( 
        COUNTROWS(
            FILTER(
                ADDCOLUMNS(
                    CALCULATETABLE(
                        SUMMARIZE(
                            StatusData , 
                            StatusData[Date] ,
                            StatusData[Asset ID] , 
                            StatusData[Asset Status] ) ,
                        StatusData[Snapshot Time] <> TIME( 16 , 0 , 0 ) ) , 
                    "@Is_Current_Flag" , 
                    IF( StatusData[Date] = MAX( StatusData[Date] ) , 1 , 0 ) ) , 
                ( StatusData[Asset Status] = "Normal Operation" && 
                [@Is_Current_Flag] = 1 ) ||
                ( StatusData[Asset Status] = "Not Operational" && 
                StatusData[Date] >= MAX( StatusData[Date] ) - 10 &&
                StatusData[Date] <= MAX( StatusData[Date] ) ) ) ) , 
            ALLEXCEPT( StatusData , StatusData[Asset ID] ) ) , 

    "@Days_Difference" , 
    DATEDIFF(

        MINX(
            CALCULATETABLE(
                FILTER(
                    ADDCOLUMNS(
                        CALCULATETABLE(
                            SUMMARIZE(
                                StatusData , 
                                StatusData[Date] ,
                                StatusData[Asset ID] , 
                                StatusData[Asset Status] ) ,
                            StatusData[Snapshot Time] <> TIME( 16 , 0 , 0 ) ) , 
                        "@Is_Current_Flag" , 
                        IF( StatusData[Date] = MAX( StatusData[Date] ) , 1 , 0 ) ) , 
                    ( StatusData[Asset Status] = "Normal Operation" && 
                    [@Is_Current_Flag] = 1 ) ||
                    ( StatusData[Asset Status] = "Not Operational" && 
                    StatusData[Date] >= MAX( StatusData[Date] ) - 10 &&
                    StatusData[Date] <= MAX( StatusData[Date] ) ) ) , 
                ALLEXCEPT( StatusData , StatusData[Asset ID] ) ) , 
            StatusData[Date] ) ,

        MAXX(
            CALCULATETABLE(
                FILTER(
                    ADDCOLUMNS(
                        CALCULATETABLE(
                            SUMMARIZE(
                                StatusData , 
                                StatusData[Date] ,
                                StatusData[Asset ID] , 
                                StatusData[Asset Status] ) ,
                            StatusData[Snapshot Time] <> TIME( 16 , 0 , 0 ) ) , 
                        "@Is_Current_Flag" , 
                        IF( StatusData[Date] = MAX( StatusData[Date] ) , 1 , 0 ) ) , 
                    ( StatusData[Asset Status] = "Normal Operation" && 
                    [@Is_Current_Flag] = 1 ) ||
                    ( StatusData[Asset Status] = "Not Operational" && 
                    StatusData[Date] >= MAX( StatusData[Date] ) - 10 &&
                    StatusData[Date] <= MAX( StatusData[Date] ) ) ) , 
                ALLEXCEPT( StatusData , StatusData[Asset ID] ) ) , 
            StatusData[Date] ) ,

        DAY ) + 1 )

VAR _fTable = 
SELECTCOLUMNS(
    FILTER(
        ADDCOLUMNS(
            _vTable , 
            "@Status_Define" ,
            SWITCH( TRUE() , 
                [Asset Status] = "Not Operational" && [@Count_Rows] = [@Days_Difference] && [@Count_Rows] <= 9 , "Short Term Not Operational" , 
                [Asset Status] = "Not Operational" && [@Count_Rows] = [@Days_Difference] && [@Count_Rows] >= 10 , "Long Term Not Operational" , 
                [Asset Status] = "Not Operational" && [@Count_Rows] <> [@Days_Difference] && [@Count_Rows] <= 9 , "Short Term Not Operational" , 
                [Asset Status] = "Normal Operation" && [@Count_Rows] = [@Days_Difference] && [@Count_Rows] =1 , "Normal Operation" ) ) , 
            StatusData[Date] = MAX( StatusData[Date] ) &&
            [@Status_Define] = "Short Term Not Operational" ) , 
        "@Status_Date" , StatusData[Date] , 
        "@Asset_ID" , StatusData[Asset ID] , 
        "@Original_Status" , StatusData[Asset Status] , 
        "@Is_Current_Flag" , [@Is_Current_Flag] ,
        "@Status_Define" , [@Status_Define] )

VAR _Results = 
COUNTROWS( _fTable )

RETURN
_Results

Not Operational Assets - Long Term = 
VAR _vTable = 
ADDCOLUMNS(
    FILTER(
        ADDCOLUMNS(
            CALCULATETABLE(
                SUMMARIZE(
                    StatusData , 
                    StatusData[Date] ,
                    StatusData[Asset ID] , 
                    StatusData[Asset Status] ) ,
                StatusData[Snapshot Time] <> TIME( 16 , 0 , 0 ) ) , 
            "@Is_Current_Flag" , 
            IF( StatusData[Date] = MAX( StatusData[Date] ) , 1 , 0 ) ) , 
        ( StatusData[Asset Status] = "Normal Operation" && 
        [@Is_Current_Flag] = 1 ) ||
        ( StatusData[Asset Status] = "Not Operational" && 
        StatusData[Date] >= MAX( StatusData[Date] ) - 10 &&
        StatusData[Date] <= MAX( StatusData[Date] ) ) ) , 
    
    "@Count_Rows" , 
    CALCULATE( 
        COUNTROWS(
            FILTER(
                ADDCOLUMNS(
                    CALCULATETABLE(
                        SUMMARIZE(
                            StatusData , 
                            StatusData[Date] ,
                            StatusData[Asset ID] , 
                            StatusData[Asset Status] ) ,
                        StatusData[Snapshot Time] <> TIME( 16 , 0 , 0 ) ) , 
                    "@Is_Current_Flag" , 
                    IF( StatusData[Date] = MAX( StatusData[Date] ) , 1 , 0 ) ) , 
                ( StatusData[Asset Status] = "Normal Operation" && 
                [@Is_Current_Flag] = 1 ) ||
                ( StatusData[Asset Status] = "Not Operational" && 
                StatusData[Date] >= MAX( StatusData[Date] ) - 10 &&
                StatusData[Date] <= MAX( StatusData[Date] ) ) ) ) , 
            ALLEXCEPT( StatusData , StatusData[Asset ID] ) ) , 

    "@Days_Difference" , 
    DATEDIFF(

        MINX(
            CALCULATETABLE(
                FILTER(
                    ADDCOLUMNS(
                        CALCULATETABLE(
                            SUMMARIZE(
                                StatusData , 
                                StatusData[Date] ,
                                StatusData[Asset ID] , 
                                StatusData[Asset Status] ) ,
                            StatusData[Snapshot Time] <> TIME( 16 , 0 , 0 ) ) , 
                        "@Is_Current_Flag" , 
                        IF( StatusData[Date] = MAX( StatusData[Date] ) , 1 , 0 ) ) , 
                    ( StatusData[Asset Status] = "Normal Operation" && 
                    [@Is_Current_Flag] = 1 ) ||
                    ( StatusData[Asset Status] = "Not Operational" && 
                    StatusData[Date] >= MAX( StatusData[Date] ) - 10 &&
                    StatusData[Date] <= MAX( StatusData[Date] ) ) ) , 
                ALLEXCEPT( StatusData , StatusData[Asset ID] ) ) , 
            StatusData[Date] ) ,

        MAXX(
            CALCULATETABLE(
                FILTER(
                    ADDCOLUMNS(
                        CALCULATETABLE(
                            SUMMARIZE(
                                StatusData , 
                                StatusData[Date] ,
                                StatusData[Asset ID] , 
                                StatusData[Asset Status] ) ,
                            StatusData[Snapshot Time] <> TIME( 16 , 0 , 0 ) ) , 
                        "@Is_Current_Flag" , 
                        IF( StatusData[Date] = MAX( StatusData[Date] ) , 1 , 0 ) ) , 
                    ( StatusData[Asset Status] = "Normal Operation" && 
                    [@Is_Current_Flag] = 1 ) ||
                    ( StatusData[Asset Status] = "Not Operational" && 
                    StatusData[Date] >= MAX( StatusData[Date] ) - 10 &&
                    StatusData[Date] <= MAX( StatusData[Date] ) ) ) , 
                ALLEXCEPT( StatusData , StatusData[Asset ID] ) ) , 
            StatusData[Date] ) ,

        DAY ) + 1 )

VAR _fTable = 
SELECTCOLUMNS(
    FILTER(
        ADDCOLUMNS(
            _vTable , 
            "@Status_Define" ,
            SWITCH( TRUE() , 
                [Asset Status] = "Not Operational" && [@Count_Rows] = [@Days_Difference] && [@Count_Rows] <= 9 , "Short Term Not Operational" , 
                [Asset Status] = "Not Operational" && [@Count_Rows] = [@Days_Difference] && [@Count_Rows] >= 10 , "Long Term Not Operational" , 
                [Asset Status] = "Not Operational" && [@Count_Rows] <> [@Days_Difference] && [@Count_Rows] <= 9 , "Short Term Not Operational" , 
                [Asset Status] = "Normal Operation" && [@Count_Rows] = [@Days_Difference] && [@Count_Rows] =1 , "Normal Operation" ) ) , 
            StatusData[Date] = MAX( StatusData[Date] ) &&
            [@Status_Define] = "Long Term Not Operational" ) , 
        "@Status_Date" , StatusData[Date] , 
        "@Asset_ID" , StatusData[Asset ID] , 
        "@Original_Status" , StatusData[Asset Status] , 
        "@Is_Current_Flag" , [@Is_Current_Flag] ,
        "@Status_Define" , [@Status_Define] )

VAR _Results = 
COUNTROWS( _fTable )

RETURN
_Results

Final Results
Final Results1273Ă—707 32.3 KB

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Short Term vs Long Term Status - Harsh v1.1.pbix (93.2 KB)

2 Likes
3

Thanks @Harsh will check this out. :+1: :magic_wand: