Column calculation - determining change in values

[JGRaiol]

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	1	6
23/04/2020	3	4	5

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!

[Ashish_Mathur]

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.

[DataZoe]

This measure should work @JGRaiol :

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

[az38]

Hi @JGRaiol 

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 )

[JGRaiol]

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 · Accepted solution]

Hi @JGRaiol ,

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

[JGRaiol]

Thank you! That worked!

Best regards