help: resetting a cumulative sum?

[pivoter]

I am trying to create a measure that is much like a cumulative sum with a reset trigger that is meaured within the dataset.

I have a status code that is tied to a datetime field. For example, status can be good or one of several reason codes. I like to know how many bad results for each reason the last good result. I am unable to filter the date results.

Measure 1:

StartDate = CALCULATE(MAX(Data[Date]),Data[Status]=good)  -- returns the time of the most current good state

Measure 2:

CALCULATE(COUNTROWS(Data),Data[Date]>[StartDate])) 

error: Calculate has been used in a T/F expression that is used as a table filter expression. This is not allowed.

I am able to get this work if I replace [StartDate] with Date(year,month,day).

I like to combine this with other filters like Data[Status]= bad-code1, badcode2, ....

[Greg_Deckler]

@pivoter - Booyah! Figured out the context issue:

BadCount = COUNTX(FILTER(goodbadugly,goodbadugly[Date]>CALCULATE(goodbadugly[GoodStartDate],ALL(goodbadugly))),goodbadugly[Date])

Basically, calculate the GoodStartDate in the context of all rows, here it is a little more readable.

BadCount1 = 
// Get latest good start date for the entire table
VAR myGoodStartDate = CALCULATE(goodbadugly[GoodStartDate],ALL(goodbadugly))
// Count the dates for rows that have a date greater than myGoodStartDate
RETURN (
COUNTX(FILTER(goodbadugly,goodbadugly[Date]>myGoodStartDate),goodbadugly[Date])
)

[pivoter]

Thanks @Greg_Deckler! I tried to recreate and could not get it to work. This is the example tables that I am trying.

Status Lookup Table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSUQpLLapUcEpMUYrViVYyBArA2EZAtnt+PpATCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [statusId = _t, Status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"statusId", Int64.Type}, {"Status", type text}})
in
    #"Changed Type"

Grouping Table (batches/event to group the data by)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSUTJRSEpMUYrViVYyBPIMk3QUDNN1FIySwEJGQKHEnByF9Px8iBpjkBqQDh0FY6hoLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [groupId = _t, Group = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"groupId", Int64.Type}, {"Group", type text}})
in
    #"Changed Type"

Data Table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc5BCsAwCETRu2Qd0BlpLxNy/2uUJiGdti7cfORpawW0cKPjKLX4mF5nhmbMbCOe712jRmxgu8jdO/PrQo5Ro/9dPoC6zFyNTKK6kf8bmRuZu2K/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, groupId = _t, statusId = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"groupId", Int64.Type}, {"statusId", Int64.Type}})
in
    #"Changed Type"

I am not familar with the X funtions, still learnng about them.

[Greg_Deckler]

Hmm, I can get you around that problem with something like this:

BadCount = COUNTX(FILTER(goodbadugly,goodbadugly[Date]>goodbadugly[GoodStartDate]),goodbadugly[Date])

But, the problem that I am having is that it returns the COUNT of all "bad" rows so trying to figure that out. This even though my "GoodStartDate" measure is working and returning the correct date. Some kind of context issue.