Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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)
Regards,
Harsh Nathani
Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!
@vsteinbahs , Try like
[Amount] -sumx(filter(table, [salesperson] =earlier([salesperson]) && eomonth([date],0) =eomonth(earlier([Date]),0)),[Amount])
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?
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)
Regards,
Harsh Nathani
Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!
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.
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |