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, sta...
Need Help
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.