cancel
Showing results for
Did you mean:
Helper I

Ola
Acumular os valores respeitando duas condições:

• A data deve ser consecutiva

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

1 ACCEPTED SOLUTION
Community Champion

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 )``````

Proud to be a Super User!

3 REPLIES 3
Community Champion

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 )``````

Proud to be a 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!

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Learn Power BI 2nd Edition

Announcements

#### 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 Design Challenge

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

#### Check it out!

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

Top Solution Authors
Top Kudoed Authors