Forum Discussion

COIL-ibesmond's avatar
COIL-ibesmond
Icon for Helper I rankHelper I
2 years ago

Creating a rolling total for consecutive days a value is negative or positive.

I've been struggling with this for some time now.  I'm trying to create a rolling total for days positive or negative.

 

I was able to create these measures, which produces a 1 if the condition is met.

 

PositiveDaysCount =

VAR vSelectedDates = VALUES('dim_Calendar'[Date])

VAR PositiveValues =

    FILTER(

        vSelectedDates,

        [Qty] > 0

    )

RETURN

    COUNTROWS(PositiveValues)

 

NegativeDaysCount =

VAR vSelectedDates = VALUES('dim_Calendar'[Date])

VAR NegativeValues =

    FILTER(

        vSelectedDates,

        [Qty] < 0

    )

RETURN

    COUNTROWS(NegativeValues)

 

But I want to create a measure that will add them up. Currently the measure repeats a 1 for each day it is consecutive, when I want it to count.  ( 1, 1, 1 vs. 1, 2, 3).  I'm trying to create 3 additional measures to achieve the results shown below for Positive Consecutive Days Count, Negative Consecutive Days Count, and Positive/Negative Consecutive Days Count. 

 

 

 

LocationRoom NoDateQtyPositive CountNegative CountPos Consec Days CountNeg Consec Days CountPos/Net Consec Days Count
113/1/2024-1 1 1-1
113/2/2024-4 1 2-2
113/3/2024-5 1 3-3
113/4/2024-27 1 4-4
113/5/2024231 1 1
113/6/2024-35 1 1-1
113/7/2024-1 1 2-2
113/8/202431 1 1
113/9/202431 2 2
113/10/2024-1 1 1-1
113/11/20240     
123/1/2024-63 1 1-1
123/2/202441 1 1
123/3/2024-18 1 1-1
123/4/20240     
123/5/2024-230 1 1-1
123/6/202451 1 1
123/7/2024-59 1 1-1
123/8/2024-927 1 1-2
123/9/2024311 1 1
Need Help

4 Replies

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

    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.

     

    • COIL-ibesmond's avatar
      COIL-ibesmond
      Icon for Helper I rankHelper I
      2 years ago

      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 )

       

      CombinedDaysCount  (Functions) =
      VAR vSelectedDates = VALUES('Custom Date Filter'[Date])
      VAR PositiveValues =
          FILTER(
              vSelectedDates,
              [Qty] > 0
          )
      VAR NegativeValues =
          FILTER(
              vSelectedDates,
              [Qty] < 0
          )
      VAR PositiveCount = COUNTROWS(PositiveValues)
      VAR NegativeCount = COUNTROWS(NegativeValues)
      RETURN
          PositiveCount - NegativeCount
       
      I tried this DAX equation also, but as explained earlier it cannot determine a gap to restart the count, and the measure doesn't take into account for separate consecutive counts for positive or negative quantities.
       
      Consecutive days of Qty =
      Var RefDate = MAX('Custom Date Filter'[Date])
      Var AllDates = All (dim_Calendar[Date])
      Var DatesWithQty = CALCULATETABLE(VALUES(fact_Inventory[Date]),ALLSELECTED())
      Var DateswithNoQty = EXCEPT(AllDates, DatesWithQty)
      Var DatesWithGap = MAXX( FILTER( DateswithNoQty, dim_Calendar[Date] <= RefDate),dim_Calendar[Date]) 
      Var MinimumDate = CALCULATE(MIN('Custom Date Filter'[Date]), REMOVEFILTERS(dim_Calendar[Date])
      Var ResultConsecutiveQtyDays = if(ISBLANK(DatesWithGap),INT(RefDate - MinimumDate+1),INT(RefDate - DatesWithGap)) 
      RETURN ResultConsecutiveQtyDays
       
       Please let me know if you or anyone in the community might have an idea.
      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        2 years ago

        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.