Cumulative measure that resets on a specific value

[bhdben]

Hi, 

I hope you are doing well.

I am trying to write a measure that will increase by one each time a condition is met and then reset to zero if another one is met. the measure is based off the QA Grade, and shoudl increase by one anythime it encounters "Unaceptable". This measure should reset back to zero when QA Grade is anything but "Unacceptable". This is the data for one property, but i want to use the measure in a matrix that has about 20 properties. The matrix shoudl show the value based on the most recentt evaluation date, and this is my only table. Ive tried to create a bool based on the QA Grade, but havent had luck making that work either. Any help is greatly appreciated 

Property	Evaluation Type	Evaluation Date	QA Score	QA Grade	SALT Grade	PIP Grade	Brand Standards Score	Cleanliness Score	Condition Score
place	Follow-up	2/16/2024	60.47%	Unacceptable	Unacceptable	Fail	79.84%	94.31%	65.59%
place	Follow-up	6/20/2023	51.76%	Unacceptable	Acceptable	Fail	44.05%	92.89%	59.29%
place	Progress	10/20/2022	80.57%	Progress	Unacceptable	Pass	100.00%	95.57%	75.27%
place	Follow-up	2/18/2022	55.22%	Unacceptable	Acceptable	Fail	35.98%	96.43%	74.90%
place	Virtual	3/5/2021	80.24%	Virtual	Virtual	Virtual	91.20%	95.27%	81.27%
place	Virtual	10/19/2020	81.79%	Virtual	Virtual	Virtual	97.48%	95.27%	81.69%
place	Regular	3/3/2020	79.3%	Acceptable	Outstanding	Pass	87.4%	95.1%	81.3%
place	Progress	4/5/2019	94.96%	Progress	Acceptable	Pass	97.90%	96.53%	91.44%
place	Progress	7/20/2018	83.92%	Progress	Acceptable		93.72%	88.22%	88.18%
place	Regular	10/5/2017	96.73%	Outstanding	Outstanding		100.00%	97.20%	94.09%
place	Regular	4/21/2017	83.49%	Acceptable	Acceptable		96.23%	86.26%	87.40%
place	Regular	7/29/2016	98.17%	Outstanding	Outstanding		100.00%	98.91%	96.21%
place	Regular	2/4/2016	84.61%	Acceptable	Acceptable		87.40%	99.24%	93.23%
place	Courtesy	8/19/2015	81.53%	Courtesy	Outstanding		100.00%	99.57%	75.35%

[SachinNandanwar · Accepted solution]

First create a measure in the main table _t :

Cnt = 
VAR _Dt =
    MAX ( _t[Evaluation Date] )
VAR _Tbl =
    FILTER ( _t, _t[Evaluation Date] <= _Dt )
VAR _cnt =
    COUNTROWS ( FILTER ( _Tbl, [QA Grade] = "Unacceptable" ) )
RETURN
    IF ( MAX ( _t[QA Grade] ) = "Unacceptable", _cnt, 0 )

 Then create a Calculated table _t_final :

_t_final = 
SUMMARIZE (
    _t,
    _t[QA Grade],
    _t[Brand Standards Score],
    _t[Cleanliness Score],
    _t[Condition Score],
    _t[Evaluation Date],
    _t[Evaluation Type],
    _t[PIP Grade],
    _t[Property],
    _t[QA Score],
    _t[SALT Grade],
    "Cnt", _t[Cnt]
)

Then create a column "Unacceptable_Series" on this table to get the final series:

Unacceptable_Series = 
VAR _currentValue = '_t_final'[Cnt]
VAR _currentDate = '_t_final'[Evaluation Date]
     
VAR _last_0 = 
CALCULATE ( 
    MAX ('_t_final'[Evaluation Date] ), 
    FILTER('_t_final', '_t_final'[Evaluation Date] < _currentDate && '_t_final'[Cnt] = 0)
)

RETURN 
IF ( 
    _currentValue = 0, 
    0, 
    CALCULATE(SUM(_t_final[Cnt]),ALLEXCEPT(_t_final,_t_final[Property]),_t_final[Evaluation 
    Date]<=_currentDate &&'_t_final'[Evaluation Date] >= _last_0
   
))

I added a few additional rows to ensure that i get the output correct

>>Sample Data
Property,Evaluation Type,Evaluation Date,QA Score,QA Grade,SALT Grade,PIP Grade,Brand Standards Score,Cleanliness Score,Condition Score
place,Follow-up,2/16/2024,60.47%,Unacceptable,Unacceptable,Fail,79.84%,94.31%,65.59%
place,Follow-up,3/16/2024,60.47%,Unacceptable,Unacceptable,Fail,89.84%,98.31%,55.59%
place,Follow-up,6/20/2023,51.76%,Unacceptable,Acceptable,Fail,44.05%,92.89%,59.29%
place,Progress,10/20/2022,80.57%,Progress,Unacceptable,Pass,100.00%,95.57%,75.27%
place,Follow-up,2/18/2022,55.22%,Unacceptable,Acceptable,Fail,35.98%,96.43%,74.90%
place,Virtual,3/5/2021,80.24%,Virtual,Virtual,Virtual,91.20%,95.27%,81.27%
place,Virtual,10/19/2020,81.79%,Virtual,Virtual,Virtual,97.48%,95.27%,81.69%
place,Regular,3/3/2020,79.3%,Acceptable,Outstanding,Pass,87.4%,95.1%,81.3%
place,Progress,4/5/2019,94.96%,Progress,Acceptable,Pass,97.90%,96.53%,91.44%
place,Progress,7/20/2018,83.92%,Progress,Acceptable,,93.72%,88.22%,88.18%
place,Regular,10/5/2017,96.73%,Outstanding,Outstanding,,100.00%,97.20%,94.09%
place,Regular,4/21/2017,83.49%,Unacceptable,Acceptable,,96.23%,86.26%,87.40%
place,Regular,7/29/2016,98.17%,Outstanding,Outstanding,,100.00%,98.91%,96.21%
place,Regular,2/4/2016,84.61%,Acceptable,Acceptable,,87.40%,99.24%,93.23%
place,Courtesy,8/19/2015,81.53%,Courtesy,Outstanding,,100.00%,99.57%,75.35%
place,Courtesy,4/16/2024,81.53%,Courtesy,Outstanding,,100.00%,99.57%,75.35%
place,Courtesy,5/16/2024,81.53%,Courtesy,Outstanding,,100.00%,99.57%,75.35%
place,Follow-up,6/16/2024,60.47%,Unacceptable,Unacceptable,Fail,89.84%,98.31%,55.59%
place,Follow-up,7/20/2023,51.76%,Unacceptable,Acceptable,Fail,44.05%,92.89%,59.29%

[Anonymous]

Hi @bhdben ,

I created a sample pbix file(see the attachment), please check if that is what you want.

1. Create a calculated column to judge if QA grade is unacceptable

UnacceptableFlag = IF('Table'[QA Grade]="Unacceptable",1,0)

2. Create a measure as below to get the culmulative values

CumulativeUnacceptable = 
VAR _date =
    SELECTEDVALUE ( 'Table'[Evaluation Date] )
VAR _property =
    SELECTEDVALUE ( 'Table'[Property] )
VAR _QAGrade =
    SELECTEDVALUE ( 'Table'[QA Grade] )
VAR _predate =
    CALCULATE (
        MAX ( 'Table'[Evaluation Date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Property] = _property
                && 'Table'[Evaluation Date] < _date
        )
    )
VAR _preunac =
    CALCULATE (
        MAX ( 'Table'[Evaluation Date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Property] = _property
                && 'Table'[Evaluation Date] < _date
                && 'Table'[QA Grade] = "Unacceptable"
        )
    )
RETURN
    IF (
        _preunac = _predate
            && _QAGrade = "Unacceptable",
        CALCULATE (
            SUM ( 'Table'[UnacceptableFlag] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[Property] = _property
                    && 'Table'[Evaluation Date] <= _predate
            )
        ),
        MAX ( 'Table'[UnacceptableFlag] )
    )

In addition, you can refer the following blog to get it:

Accumulative, conditional accumulative in Power Qu... - Microsoft Fabric Community

Best Regards

[SachinNandanwar]

If there are 3 continous Unacceptable followed by anything but "Unacceptable"  should the measure display be like this ?
QA Grade              Measure

Unacceptable         0
Unacceptable         1
Unacceptable         2
Progress                 0

and how do you define the order of QA Grade ?

[bhdben]

yes