Forum Discussion

vsteinbahs's avatar
vsteinbahs
Icon for Helper I rankHelper I
6 years ago
Solved

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

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 app...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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/1143370


    Regards,

    Harsh Nathani


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

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

vsteinbahs's avatar
vsteinbahs
Icon for Helper I rankHelper I
6 years ago

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?

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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/1143370


Regards,

Harsh Nathani


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

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

  • vsteinbahs's avatar
    vsteinbahs
    Icon for Helper I rankHelper I
    6 years ago

    Hi Anonymous 

    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.