Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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 |
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)
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 |
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])))
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!
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))
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
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)
Thank you!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |