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):

datevalue1value2value3
24/04/2020416
23/04/2020345

 

Values 1, 2 and 3 are parameters related to my industry.

 

I need a new column, named "analysis", that shall perform the following calculation:

 

analysis (today) = (value1(yesterday) + value2(yesterday)) - (value1(today) + value2(today)) + (value3(today) - value3(yesterday))

 

In words, the new column shall be calculated as the change in the sum of values 1 and 2, plus the addition occured on value 3.

 

In my example, the "analysis" result for "24/04/2020":

 

analysis_24/04/2020 = (3+4) - (4+1) + (6-5) = 7 - 5 + 1 = 3

 

Any insights? I've tried using FILTER combined with SELECTCOLUMNS, but considering a have a real table with far more than 2 rows, this is not working.

 

Thanks in advance!

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

6 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Hi Anonymous 

    it could be a column like

    Column = 
var _thisDate = Table[Date]
var _prevDate = CALCULATE(MAX(Table[Date]), Table[Date] < _thisDate)
var _prevVal1 = CALCULATE(MAX(Table[Value1]), Table[Date] = _prevDate)
var _prevVal2 = CALCULATE(MAX(Table[Value2]), Table[Date] = _prevDate)
var _prevVal3 = CALCULATE(MAX(Table[Value3]), Table[Date] = _prevDate)
RETURN
_prevVal1 + _prevVal2 - ([Value1] + [Value2]) + ([Value3] - _prevVal3 )
    • 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
  • DataZoe's avatar
    DataZoe
    Microsoft Employee
    6 years ago

    This measure should work Anonymous :

     

    Analysis = 
sumx(values(analysistable[Date]),
(calculate(sum(analysistable[v1]),PREVIOUSDAY(analysistable[Date]))
    +calculate(sum(analysistable[v2]),PREVIOUSDAY(analysistable[Date])))
-
(sum(analysistable[v1])
    +sum(analysistable[v2]))
+
(sum(analysistable[v3])
    -calculate(sum(analysistable[v3]),PREVIOUSDAY(analysistable[Date]))))
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    Hi,

    I added a row for 25 April 2020 with the following information 6,3,4.  Here is the result i got.  You may download my PBI file from here.