How to create a calculated column for monthly sales difference per sales person

[vsteinbahs]

Hi,

I'm trying to add a calculated column to show the sales amount difference between current month and previous month per sales person, as shown in the attached screen pic.  Any help is greatly appreciated.

[amitchandak]

@vsteinbahs , Try like

[Amount] -sumx(filter(table, [salesperson] =earlier([salesperson]) && eomonth([date],0) =eomonth(earlier([Date]),0)),[Amount])

[vsteinbahs]

Hi @amitchandak 

Thank you for such quick reply.  I tried to apply the command you proposed, but it doesn't seem to work, as shown in pic below.  Here is the link to actual pbix file.  Any ideas how to fix?

[harshnathani · Accepted solution]

Hi @vsteinbahs ,

Column = 
var prevDate = CALCULATE(MAX('Table'[Date]), FILTER ('Table','Table'[SalesPerson] = EARLIER('Table'[SalesPerson]) && 'Table'[Date] < EARLIER('Table'[Date])))

var prevamount = CALCULATE(MAX('Table'[Amount]), FILTER('Table','Table'[SalesPerson] = EARLIER('Table'[SalesPerson]) && 'Table'[Date]  = prevDate))
RETURN

IF (prevamount = BLANK(), BLANK(),
'Table'[Amount] - prevamount)

https://community.powerbi.com/t5/Community-Blog/Getting-Previous-Values-in-Power-BI-Part-1/ba-p/1143...

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

[vsteinbahs]

Hi @harshnathani 

Thank you very much for such a quick response and solution!  I've tested it on few data tables, and it does work as expected.