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!

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.