Hello @DavieJoe,
Thank You for posting your query onto the Forum.
In order to achieve the results based on the requirements that you’ve mentioned i.e., “Short Term Not Operational” and “Long Term Not Operational”. Below are the two DAX measures alongwith the screenshot of the final results provided -
Not Operational Assets - Short Term =
VAR _vTable =
ADDCOLUMNS(
FILTER(
SUMMARIZE(
StatusData ,
StatusData[Date] ,
StatusData[Asset ID] ,
StatusData[Status] ,
StatusData[Is Current Flag] ) ,
( StatusData[Status] = "Operational" &&
StatusData[Is Current Flag] = 1 ) ||
( StatusData[Status] = "Not Operational" &&
StatusData[Date] >= MAX( StatusData[Date] ) - 10 &&
StatusData[Date] <= MAX( StatusData[Date] ) ) ) ,
"@Count_Rows" ,
CALCULATE(
COUNTROWS(
FILTER(
SUMMARIZE(
StatusData ,
StatusData[Date] ,
StatusData[Asset ID] ,
StatusData[Status] ,
StatusData[Is Current Flag] ) ,
( StatusData[Status] = "Operational" &&
StatusData[Is Current Flag] = 1 ) ||
( StatusData[Status] = "Not Operational" &&
StatusData[Date] >= MAX( StatusData[Date] ) - 10 &&
StatusData[Date] <= MAX( StatusData[Date] ) ) ) ) ,
ALLEXCEPT( StatusData , StatusData[Asset ID] ) ) ,
"@Days_Diff" ,
DATEDIFF(
MINX(
CALCULATETABLE(
FILTER(
SUMMARIZE(
StatusData ,
StatusData[Date] ,
StatusData[Asset ID] ,
StatusData[Status] ,
StatusData[Is Current Flag] ) ,
( StatusData[Status] = "Operational" &&
StatusData[Is Current Flag] = 1 ) ||
( StatusData[Status] = "Not Operational" &&
StatusData[Date] >= MAX( StatusData[Date] ) - 10 &&
StatusData[Date] <= MAX( StatusData[Date] ) ) ) ,
ALLEXCEPT( StatusData , StatusData[Asset ID] ) ) ,
StatusData[Date] ) ,
MAXX(
CALCULATETABLE(
FILTER(
SUMMARIZE(
StatusData ,
StatusData[Date] ,
StatusData[Asset ID] ,
StatusData[Status] ,
StatusData[Is Current Flag] ) ,
( StatusData[Status] = "Operational" &&
StatusData[Is Current Flag] = 1 ) ||
( StatusData[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() ,
[Status] = "Not Operational" && [@Count_Rows] = [@Days_Diff] && [@Count_Rows] <= 9 , "Short Term Not Operational" ,
[Status] = "Not Operational" && [@Count_Rows] = [@Days_Diff] && [@Count_Rows] >= 10 , "Long Term Not Operational" ,
[Status] = "Not Operational" && [@Count_Rows] <> [@Days_Diff] && [@Count_Rows] <= 9 , "Short Term Not Operational" ,
[Status] = "Operational" && [@Count_Rows] = [@Days_Diff] && [@Count_Rows] =1 , "Operational" ) ) ,
StatusData[Date] = MAX( StatusData[Date] ) &&
[@Status_Define] = "Short Term Not Operational" ) ,
"@Status_Date" , StatusData[Date] ,
"@Asset_ID" , StatusData[Asset ID] ,
"@Original_Status" , StatusData[Status] ,
"@Is_Current_Flag" , StatusData[Is Current Flag] ,
"@Status_Define" , [@Status_Define] )
VAR _Results =
COUNTROWS( _fTable )
RETURN
_Results
Not Operational Assets - Long Term =
VAR _vTable =
ADDCOLUMNS(
FILTER(
SUMMARIZE(
StatusData ,
StatusData[Date] ,
StatusData[Asset ID] ,
StatusData[Status] ,
StatusData[Is Current Flag] ) ,
( StatusData[Status] = "Operational" &&
StatusData[Is Current Flag] = 1 ) ||
( StatusData[Status] = "Not Operational" &&
StatusData[Date] >= MAX( StatusData[Date] ) - 10 &&
StatusData[Date] <= MAX( StatusData[Date] ) ) ) ,
"@Count_Rows" ,
CALCULATE(
COUNTROWS(
FILTER(
SUMMARIZE(
StatusData ,
StatusData[Date] ,
StatusData[Asset ID] ,
StatusData[Status] ,
StatusData[Is Current Flag] ) ,
( StatusData[Status] = "Operational" &&
StatusData[Is Current Flag] = 1 ) ||
( StatusData[Status] = "Not Operational" &&
StatusData[Date] >= MAX( StatusData[Date] ) - 10 &&
StatusData[Date] <= MAX( StatusData[Date] ) ) ) ) ,
ALLEXCEPT( StatusData , StatusData[Asset ID] ) ) ,
"@Days_Diff" ,
DATEDIFF(
MINX(
CALCULATETABLE(
FILTER(
SUMMARIZE(
StatusData ,
StatusData[Date] ,
StatusData[Asset ID] ,
StatusData[Status] ,
StatusData[Is Current Flag] ) ,
( StatusData[Status] = "Operational" &&
StatusData[Is Current Flag] = 1 ) ||
( StatusData[Status] = "Not Operational" &&
StatusData[Date] >= MAX( StatusData[Date] ) - 10 &&
StatusData[Date] <= MAX( StatusData[Date] ) ) ) ,
ALLEXCEPT( StatusData , StatusData[Asset ID] ) ) ,
StatusData[Date] ) ,
MAXX(
CALCULATETABLE(
FILTER(
SUMMARIZE(
StatusData ,
StatusData[Date] ,
StatusData[Asset ID] ,
StatusData[Status] ,
StatusData[Is Current Flag] ) ,
( StatusData[Status] = "Operational" &&
StatusData[Is Current Flag] = 1 ) ||
( StatusData[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() ,
[Status] = "Not Operational" && [@Count_Rows] = [@Days_Diff] && [@Count_Rows] <= 9 , "Short Term Not Operational" ,
[Status] = "Not Operational" && [@Count_Rows] = [@Days_Diff] && [@Count_Rows] >= 10 , "Long Term Not Operational" ,
[Status] = "Not Operational" && [@Count_Rows] <> [@Days_Diff] && [@Count_Rows] <= 9 , "Short Term Not Operational" ,
[Status] = "Operational" && [@Count_Rows] = [@Days_Diff] && [@Count_Rows] =1 , "Operational" ) ) ,
StatusData[Date] = MAX( StatusData[Date] ) &&
[@Status_Define] = "Long Term Not Operational" ) ,
"@Status_Date" , StatusData[Date] ,
"@Asset_ID" , StatusData[Asset ID] ,
"@Original_Status" , StatusData[Status] ,
"@Is_Current_Flag" , StatusData[Is Current Flag] ,
"@Status_Define" , [@Status_Define] )
VAR _Results =
COUNTROWS( _fTable )
RETURN
_Results
I’m also attaching the working of the PBIX file for the reference purposes.
Hoping you find this useful and meets your requirements that you’ve been looking for.
Note:- In the case of 60 days requirements, change the number of days condition accordingly in both the measures.
Thanks and Warm Regards,
Harsh
Short Term vs Long Term Status - Harsh.pbix (89.8 KB)