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 datas...
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)

     

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)

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

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

      Anonymous 

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

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

      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