Calculate Difference Based on Last Entry (Date and Time Column)

[carlochecchia]

Hi there,

I have the following table and would like to get some help in regards to how to calculate the column called 'Change'
I would like to create a Dax Measure that calculates the difference between the value in column NetProducts and the previous value available.

Thanks in advance!

DateTime	NetProducts	Change
22/07/2021 20:30	1.780.022	--
22/07/2021 20:35	1.780.022	0
22/07/2021 21:00	1.780.037	-15
22/07/2021 21:04	1.780.037	0
22/07/2021 21:05	1.780.037	0
22/07/2021 21:15	1.780.037	0
22/07/2021 21:19	1.780.037	0
22/07/2021 21:42	1.780.049	-12
22/07/2021 21:42	1.780.049	0
22/07/2021 21:56	1780049	0
22/07/2021 22:02	1780049	0
22/07/2021 22:12	1780049	0
22/07/2021 22:46	1780064	-15

[carlochecchia · Accepted solution]

In case it was tough to understand the data, here it goes again

DateTime		NetProducts		Change
22/07/2021 20:30		1.780.022		--
22/07/2021 20:35		1.780.022		0
22/07/2021 21:00		1.780.037		-15
22/07/2021 21:04		1.780.037		0
22/07/2021 21:05		1.780.037		0
22/07/2021 21:15		1.780.037		0
22/07/2021 21:19		1.780.037		0
22/07/2021 21:42		1.780.049		-12
22/07/2021 21:42		1.780.049		0
22/07/2021 21:56		1.780.049		0
22/07/2021 22:02		1.780.049		0
22/07/2021 22:12		1.780.049		0
22/07/2021 22:46		1.780.064		-15

[carlochecchia · Accepted solution]

In case it was tough to understand the data, here it goes again

DateTime		NetProducts		Change
22/07/2021 20:30		1.780.022		--
22/07/2021 20:35		1.780.022		0
22/07/2021 21:00		1.780.037		-15
22/07/2021 21:04		1.780.037		0
22/07/2021 21:05		1.780.037		0
22/07/2021 21:15		1.780.037		0
22/07/2021 21:19		1.780.037		0
22/07/2021 21:42		1.780.049		-12
22/07/2021 21:42		1.780.049		0
22/07/2021 21:56		1.780.049		0
22/07/2021 22:02		1.780.049		0
22/07/2021 22:12		1.780.049		0
22/07/2021 22:46		1.780.064		-15

[sevenhills]

Create the measure as below and format the measure as whole number. 

Change the names to your needs

Change = 
var _pValDT = Maxx(Filter( ALLSELECTED(TableChangeCalc),  TableChangeCalc[DateTime] < SELECTEDVALUE(TableChangeCalc[DateTime]) ), TableChangeCalc[DateTime])

var _pValDT_Val = Maxx(Filter( ALLSELECTED(TableChangeCalc),  TableChangeCalc[DateTime] = _pValDT), TableChangeCalc[NetProducts])

Return IF ( ISBLANK(_pValDT_Val), "--", ( _pValDT_Val - SELECTEDVALUE(TableChangeCalc[NetProducts])))

[carlochecchia]

Thank you so much. Your DAX formula did the job.
I was wondering if you could help me improve this DAX measure to take in account the value for the last date/hour based on a specific store branch.

It´s the same scenario described above, but with an extra column that has information on the store branch.
I would like the DAX calculation to take into account the store branch´s last available data.

I would like to obtain the results from the second table in my image (change column).
As opposed to what i am getting with the results in the change calculation (third table from my image)

Thanks in advance for the help!

[sevenhills]

Could you attach the pbix file (removing sensitive info) so that I can look into it ? Thanks

Or try this ... 

Change2 = 
var _cValDT = SELECTEDVALUE('TableChangeCalc (2)'[DateTime])
var _cValStore = SELECTEDVALUE('TableChangeCalc (2)'[Store])
var _cVal = SELECTEDVALUE('TableChangeCalc (2)'[NetProducts])

var _pValDT = Maxx(Filter( ALLSELECTED('TableChangeCalc (2)'),  'TableChangeCalc (2)'[DateTime] < _cValDT && 'TableChangeCalc (2)'[Store] = _cValStore), 'TableChangeCalc (2)'[DateTime])

var _pValDT_Val = Maxx(Filter( ALLSELECTED('TableChangeCalc (2)'),  'TableChangeCalc (2)'[DateTime] = _pValDT && 'TableChangeCalc (2)'[Store] = _cValStore), 'TableChangeCalc (2)'[NetProducts])

Return IF ( ISBLANK(_pValDT_Val), "--", ( _pValDT_Val - _cVal))

[carlochecchia]

https://checonpesquisa-my.sharepoint.com/:u:/g/personal/carlo_checonpesquisa_com_br/EZCKM4B4Ti1Atm7M...

I would like to get the result from the two tables on the right, on the table on the lefthandside 

[sevenhills · Accepted solution]

Change2 = 
var _cValDT = SELECTEDVALUE('Table'[DateTime])
var _cValStore = SELECTEDVALUE('Table'[Store])
var _cVal = SELECTEDVALUE('Table'[NetProducts])
var _pValDT = Maxx(Filter( ALLSELECTED('Table'),  'Table'[DateTime] < _cValDT && 'Table'[Store] = _cValStore), 'Table'[DateTime])
var _pValDT_Val = Maxx(Filter( ALLSELECTED('Table'),  'Table'[DateTime] = _pValDT && 'Table'[Store] = _cValStore), 'Table'[NetProducts])
Return IF ( ISBLANK(_pValDT_Val), "--", ( _cVal - _pValDT_Val))

Mark this one as answer to the solution. (I saw you marked your question post as answer)

[carlochecchia]

Thank you!