Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Column calculation - determining change in values

Hello all!    I'd like to address a certain issue I am facing w/ Power BI.   I have a table in the following format (date format dd/mm/yyyy): date value1 value2 value3 24/04/2020 4...
Need Help
  • camargos88's avatar
    camargos88
    6 years ago

    Hi Anonymous ,

     

    Try this calculated column:

     

    Column =
    VAR _date = 'Table'[date]
    VAR _lastDate = CALCULATE(MAX('Table'[date]); FILTER(ALL('Table'); 'Table'[date] < _date))
    VAR _tb = FILTER(ALL('Table'); 'Table'[date] = _lastDate)
    RETURN
    IF(COUNTROWS(_tb) > 0;
    (SELECTCOLUMNS(_tb; "V1"; 'Table'[value1]) + SELECTCOLUMNS(_tb; "V2"; 'Table'[value2])) -
    ('Table'[value1] + 'Table'[value2]) +
    ('Table'[value3] - SELECTCOLUMNS(_tb; "V3"; 'Table'[value3]));
    BLANK())
     
    Ricardo
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hello az38, thank you so much for your reply!

 

I perfectly understood your implementation, but when I put it on my BI, all prevdate and prevVals are returning blank, and not the proper value. I am implementing this as a calculated column.

 

Any insights?

 

Many thanks!

camargos88's avatar
camargos88
Community Champion
6 years ago

Hi Anonymous ,

 

Try this calculated column:

 

Column =
VAR _date = 'Table'[date]
VAR _lastDate = CALCULATE(MAX('Table'[date]); FILTER(ALL('Table'); 'Table'[date] < _date))
VAR _tb = FILTER(ALL('Table'); 'Table'[date] = _lastDate)
RETURN
IF(COUNTROWS(_tb) > 0;
(SELECTCOLUMNS(_tb; "V1"; 'Table'[value1]) + SELECTCOLUMNS(_tb; "V2"; 'Table'[value2])) -
('Table'[value1] + 'Table'[value2]) +
('Table'[value3] - SELECTCOLUMNS(_tb; "V3"; 'Table'[value3]));
BLANK())
 
Ricardo
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thank you! That worked!

     

    Best regards