cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Vilmar
Helper I
Helper I

Acumulado com Condição

Ola
Acumular os valores respeitando duas condições:

  • A data deve ser consecutiva

  • Deve respeitar o sinal (acumular somente positivos ou somente negativos)

Sem título.png

1 ACCEPTED SOLUTION
camargos88
Community Champion
Community Champion

@Vilmar ,

Try using this measurement:

_Esperado = 
VAR _currentDate = MAX ('Table'[Data])
VAR _currentValue = MIN ('Table'[Valor])
VAR _minDate = MINX('Table', 'Table'[Data])
VAR _lastNegative = CALCULATE(MAX('Table'[Data]), FILTER(ALL('Table'), 'Table'[Data] < _currentDate && 'Table'[Valor] < 0))
VAR _lastPositive = CALCULATE(MAX('Table'[Data]), FILTER(ALL('Table'), 'Table'[Data] < _currentDate && 'Table'[Valor] >= 0))
VAR _tbConsecutiveDate = 
            FILTER(
                SUMMARIZE(
                    FILTER(ALL('Table'), 'Table'[Data] <= _currentDate),
                    'Table'[Data], 
                    "NextDate", DATEDIFF('Table'[Data], CALCULATE(MIN('Table'[Data]), FILTER(ALL('Table'), 'Table'[Data] > EARLIER('Table'[Data]))), DAY)
                ),
                [NextDate] = 1 || 'Table'[Data] = _currentDate
            )
VAR _positivesValues =
    CALCULATE (
        SUMX ( FILTER ( 'Table', 'Table'[Valor] >= 0 ), 'Table'[Valor] ),
        FILTER ( ALL ( 'Table'[Data] ), 'Table'[Data] > _lastNegative && 'Table'[Data] <= _currentDate), _tbConsecutiveDate
    )
VAR _negativeValues =
    CALCULATE (
        SUMX ( FILTER ( 'Table', 'Table'[Valor] < 0 ), 'Table'[Valor] ),
        FILTER ( ALL ( 'Table'[Data] ), 'Table'[Data] > _lastPositive && 'Table'[Data] <= _currentDate), _tbConsecutiveDate
    )
RETURN  
    IF ( _currentValue < 0, _negativeValues, _positivesValues )

Capture.PNG



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

3 REPLIES 3
camargos88
Community Champion
Community Champion

@Vilmar ,

Try using this measurement:

_Esperado = 
VAR _currentDate = MAX ('Table'[Data])
VAR _currentValue = MIN ('Table'[Valor])
VAR _minDate = MINX('Table', 'Table'[Data])
VAR _lastNegative = CALCULATE(MAX('Table'[Data]), FILTER(ALL('Table'), 'Table'[Data] < _currentDate && 'Table'[Valor] < 0))
VAR _lastPositive = CALCULATE(MAX('Table'[Data]), FILTER(ALL('Table'), 'Table'[Data] < _currentDate && 'Table'[Valor] >= 0))
VAR _tbConsecutiveDate = 
            FILTER(
                SUMMARIZE(
                    FILTER(ALL('Table'), 'Table'[Data] <= _currentDate),
                    'Table'[Data], 
                    "NextDate", DATEDIFF('Table'[Data], CALCULATE(MIN('Table'[Data]), FILTER(ALL('Table'), 'Table'[Data] > EARLIER('Table'[Data]))), DAY)
                ),
                [NextDate] = 1 || 'Table'[Data] = _currentDate
            )
VAR _positivesValues =
    CALCULATE (
        SUMX ( FILTER ( 'Table', 'Table'[Valor] >= 0 ), 'Table'[Valor] ),
        FILTER ( ALL ( 'Table'[Data] ), 'Table'[Data] > _lastNegative && 'Table'[Data] <= _currentDate), _tbConsecutiveDate
    )
VAR _negativeValues =
    CALCULATE (
        SUMX ( FILTER ( 'Table', 'Table'[Valor] < 0 ), 'Table'[Valor] ),
        FILTER ( ALL ( 'Table'[Data] ), 'Table'[Data] > _lastPositive && 'Table'[Data] <= _currentDate), _tbConsecutiveDate
    )
RETURN  
    IF ( _currentValue < 0, _negativeValues, _positivesValues )

Capture.PNG



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



mahoneypat
Super User
Super User

Not sure if this is what you mean, but please try this measure expressions below in a table visual with your Date column.  Replace Table with your actual table name.  If not, please clarify further.

 

Esperado =
VAR thisdate =
    MAX ( Table[Data] )
VAR thisvalue =
    MIN ( Table[Valor] )
VAR positives =
    CALCULATE (
        SUMX ( FILTER ( Table, Table[Valor] >= 0 ), Table[Valor] ),
        FILTER ( ALLSELECTED ( Table[Data] ), Table[Data] <= thisdate )
    )
VAR negatives =
    CALCULATE (
        SUMX ( FILTER ( Table, Table[Valor] < 0 ), Table[Valor] ),
        FILTER ( ALLSELECTED ( Table[Data] ), Table[Data] <= thisdate )
    )
RETURN
    IF ( thisvalue < 0, negatives, positives )

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Greg_Deckler
Super User
Super User

@Vilmar - Pode não parecer no início, mas acredito que você precisa de uma variação de Cthulhu:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Cthulhu/m-p/509739#M211


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!