Identifying shifts in data trend

[Anonymous]

Hello all! 

I'm fairly new on PowerBI development and currently I am facing an issue that I am not sure on how to solve it (actually I am not even sure if it is solvable).

I have a certain dataset that obeys the following pattern: the data accumulates, and at certain date, it is restarted for a random/lower value. For example:

date	value
01/04/2020	123
02/04/2020	145
03/04/2020	190
04/04/2020	214
05/04/2020	259
06/04/2020	399
07/04/2020	450
08/04/2020	121
09/04/2020	220
10/04/2020	330
11/04/2020	390

If you notice, from 07/04 to 08/04, the values stopped accumulating and went back to a lower value.

I need an implementation that can identify the dates at which this shift in trend occurs. In this example, the implementation should identify 08/04/2020.

What is the best way to implement this? It works for me either as a column or a measure.

Thanks in advance!

[camargos88 · Accepted solution]

Hi @Anonymous ,

Try this code to create a new column:

Change =

VAR _actualValue = 'Table'[value]

VAR _date = 'Table'[date]

VAR _lastDate = CALCULATE(MAX('Table'[date]); FILTER(ALL('Table'); 'Table'[date] < _date))

VAR _previousValue = CALCULATE(DISTINCT('Table'[value]); FILTER(ALL('Table'); 'Table'[date] = _lastDate))

RETURN IF(_actualValue < _previousValue; 1; 0)

Ricardo

[Syndicate_Admin]

Estimates, you can in power bi find patterns or trends example: in a table egress of people, which finds patterns of egresses, example age, sex, city

Best regards

[camargos88 · Accepted solution]

Hi @Anonymous ,

Try this code to create a new column:

Change =

VAR _actualValue = 'Table'[value]

VAR _date = 'Table'[date]

VAR _lastDate = CALCULATE(MAX('Table'[date]); FILTER(ALL('Table'); 'Table'[date] < _date))

VAR _previousValue = CALCULATE(DISTINCT('Table'[value]); FILTER(ALL('Table'); 'Table'[date] = _lastDate))

RETURN IF(_actualValue < _previousValue; 1; 0)

Ricardo

[Anonymous]

Hi @camargos88, thank you for the reply!!

I created this column and it worked returning "1" at the turning points. However, I wanted the column to return the date at which the shift occurred.

I changed the code in the RETURNIF as follows:

RETURN IF(_actualValue < _previousValue; 'Table'[date]; 0)

However, it is returning a random value, not the date. Any suggestions?

Thank you!

[az38 · Accepted solution]

@camargos88 

had a great solution, accept it as solution

for your need try

RETURN 
IF(_actualValue < _previousValue; _actualValue ; 0)

[Anonymous]

Thank you guys for the help.

@az38 when I typed what you suggested, the return is "0" for all rows. 

I figured that I have tried was not working because the column was configured as a number, and not a date. I changed this and it worked.

Thanks again!

[az38]

@Anonymous 

it's great that you were able to deal with data type by yourself! well done!

[camargos88]

Hi @Anonymous ,

try this new code:

Change =

VAR _actualValue = 'Table'[value]

VAR _date = 'Table'[date]

VAR _lastDate = CALCULATE(MAX('Table'[date]); FILTER(ALL('Table'); 'Table'[date] < _date))

VAR _previousValue = CALCULATE(DISTINCT('Table'[value]); FILTER(ALL('Table'); 'Table'[date] = _lastDate))

RETURN IF(_actualValue < _previousValue; _date; BLANK())

Ricardo