topic Calculate difference between values in different columns in DAX Commands and Tips

Calculate difference between values in different columns

Anonymous — Sat, 14 Dec 2019 13:35:39 GMT

Hi,

I would like to build the Excel visualization (see screenshot) in PBI. I assume this is possible with a DAX measure? I've been playing around with some DAX functions and tried some solutions from previous posts, but I cannot get my head around it.

It basically needs to calculate the difference in Amount compared to the Amount of the previous date. It should be dynamic; new dates can get added to the table.

Any hints, tips are appreciated.

Best regards,

Armand

Re: Calculate difference between values in different columns

az38 — Sat, 14 Dec 2019 21:09:26 GMT

Hi Anonymous

try a measure

diff = SELECTEDVALUE('Table'[Amount])-calculate(FIRSTNONBLANK('Table'[Amount];1);filter(ALLEXCEPT('Table';'Table'[Article]);'Table'[Date]<selectedvalue('Table'[Date])))

do not hesitate to give a kudo to useful posts and mark solutions as solution

Re: Calculate difference between values in different columns

Anonymous — Mon, 16 Dec 2019 12:17:16 GMT

az38 thank you for your proposal, but unfortunately it did not work out.

Eventually I found the solution using the following measures:

PreviousDate
Amount previous date
Diff with prev date
Diff %

PreviousDate = VAR CurrentDate = SELECTEDVALUE ( 'Sheet1'[Date] ) RETURN CALCULATE ( MAX ( 'Sheet1'[Date] ); ALLSELECTED ( Sheet1 ); KEEPFILTERS ( Sheet1[Date] < CurrentDate ) )

Amount previous date = VAR Prev = [PreviousDate] RETURN CALCULATE ( [Total Amount]; Sheet1[Date] = Prev )

Diff with previous = [Total Amount] - [Amount previous date]

Diff % = IF ( ISBLANK ( [Diff with previous] ); BLANK (); DIVIDE ( [Diff with previous]; [Amount previous date] ) * 100 )