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

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!

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