Forum Discussion
Creating a rolling total for consecutive days a value is negative or positive.
COIL-ibesmond This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149
The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.
In your case, sounds like you would want to use COUNTROWS along with a filter. You may also need something like Cthulhu Cthulhu - Microsoft Fabric Community to identify when you encounter a "change" condition.
Thanks for the post Greg_Deckler. I came across similar posts by you during my search. I've also spent some time looking at Alberto Ferrari's rsolutions to consecutive counts. https://www.youtube.com/watch?v=E5VZ6k9kk60 & https://www.youtube.com/watch?v=GR9ROCQVyLk.
I exhausted ChatGPT without success. I went back and looked at my model and thought about two issues that might cause irregular behavior. The first thing was I built a custom Date Filtering Table which has dates for Custom, YTD, 30 Days back and MTD. Because I have more than one date in the table, I created a bi-directional many-to-one relationship to my date table which then has a one-to-many relationship to my fact table. After breaking down the variables in my measure, I don't see an issue with the modeling. I think the issue is related to my fact table. It looks like I have a record for every day, so when the measure is looking for a gap it can't find one because the record is returning 0.
I was able to create this measure below to tally a 1 or -1 for each day the quantity is positive or negative, but can't figure out how to modify the measure to result in a sequencial count for consecutive days, eg. (1, 2, 3, vs. 1,1,1 )
- Greg_Deckler2 years ago
Community Champion
COIL-ibesmond Sounds like a unique sort of data model. Any chance you can post a link to a sample PBIX or some sample data as well as a picture of your semantic model. Hard to visualize exactly what you are dealing with and what could be going wrong.
- COIL-ibesmond2 years ago
Helper I
I mocked up a file but I can't attach a xlsx or pbix to the forum yet. I'll post it over at EDNA.