Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Vilmar
Resolver I
Resolver 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
Employee
Employee

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.