Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Identifying shifts in data trend

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:

 

datevalue
01/04/2020123
02/04/2020145
03/04/2020190
04/04/2020214
05/04/2020259
06/04/2020399
07/04/2020450
08/04/2020121
09/04/2020220
10/04/2020330
11/04/2020390

 

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!

Need Help
  • camargos88's avatar
    camargos88
    6 years ago

    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
  • az38's avatar
    az38
    6 years ago

    camargos88 

    had a great solution, accept it as solution

    for your need try

    RETURN 
IF(_actualValue < _previousValue; _actualValue ; 0)

     

7 Replies

  • camargos88's avatar
    camargos88
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        az38
        Icon for Community Champion rankCommunity Champion
        6 years ago

        camargos88 

        had a great solution, accept it as solution

        for your need try

        RETURN 
IF(_actualValue < _previousValue; _actualValue ; 0)

         

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Icon for Administrator rankAdministrator
    4 years ago

    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