Forum Discussion

pivoter's avatar
pivoter
New Member
10 years ago

help: resetting a cumulative sum?

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, ....

 

 

 

Need Help

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    10 years ago

    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.

     

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    10 years ago

    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's avatar
      pivoter
      New Member
      10 years ago

      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.