Forum Discussion

Mp1977's avatar
Mp1977
Helper II
3 years ago

Consecutive days with negative values

Hi !

I am trying to calculate the max quantity of consecutive negative values.

I managed to create a measure (sqlbi video) that when I use it in a table it works fine, but I need to get the same result in a card, but I could not find a way.


here is my model:  https://1drv.ms/f/s!AvNOngq_bKmCsN455nL6O9Fhm4jSUA?e=D7aMzW

all help appreciatted

Need Help

3 Replies

  • DOLEARY85's avatar
    DOLEARY85
    Resident Rockstar
    3 years ago

    Hi

     

    it looks like the measure when used in a card is giving you the current consecutive days negative. if you scroll down your table to 21 june 23 (your last record) you'll see that 2 is the current number of consecutive days the value is negative.

     

    To get the max number for the card, create a new measure:

     

    Measure =
    MAXX(VALUES('Grupo Empate'[Date]),[consecutive negative days])
     
    If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

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