Finding the job title number of each salesperson in the previous month

[Anonymous]

Hi all,

I have the example (not real) data below, with the aim to find the job title number of each salesperson in the previous month

In the table below, I have assigned the title number: 'Junior Sales Associate' as 1 and 'Senior Sales Associate' as 2;

and the salesperson number: Salesperson 'A' as 1 and 'B' as 2

Now I want to create a calculated column called 'Last Month Title Number', i.e. the title number of EACH salesperson in the precious month. I tried the DAX below but returns blank:

Last Month Title Number =

VAR LastMonth = MAXX(FILTER('Salesperson and commission','Salesperson and commission'[Month]<EARLIER('Salesperson and commission'[Month])),'Salesperson and commission'[Month])

VAR Salesperson = MAXX(FILTER('Salesperson and commission','Salesperson and commission'[Salesperson number]=earlier('Salesperson and commission'[Salesperson number])),'Salesperson and commission'[Salesperson number])

return

CALCULATE(sum('Salesperson and commission'[Title Number]),'Salesperson and commission'[Month]=LastMonth,'Salesperson and commission'[Salesperson number]=Salesperson)

Salesperson	Month	Comission ($)	Title	Title Number	Salesperson number
A	2020/1/31	3000	Junior Sales Associate	1	1
A	2020/2/29	1000	Senior Sales Associate	2	1
A	2020/3/31	2000	Senior Sales Associate	2	1
B	2020/1/31	7000	Junior Sales Associate	1	2
B	2020/2/29	6000	Junior Sales Associate	1	2
B	2020/3/31	9000	Senior Sales Associate	2	2

How should I revise the DAX? Thanks a lot for your help in advance!

[Anonymous · Accepted solution]

This is the code that gets you the prev month's title number. This should be fast on big tables as it does not use CALCULATE.

[Prev Tittle Number] = // calculated column
var __salespersonNumber = T[Salesperson Number]
var __currentDate = T[Month] // this must be the date data type
var __prevTitleNumber =
	MAXX(
		topn(1,
			filter(
				T,
				T[Salesperson Number] = __salespersonNumber,
				T[Month] < __currentDate
			),
			T[Month],
			DESC
		),
		T[Title Number]
	)
return
	__prevTitleNumber

Best

D

[nandukrishnavs]

@Anonymous 

Try this DAX

Previous Title = 
var _sPerson= CALCULATE(SELECTEDVALUE('Table'[Salesperson]))
var _smonth= CALCULATE(SELECTEDVALUE('Table'[Month]))
var _previousTitle= LOOKUPVALUE('Table'[Title Number],'Table'[Month],EOMONTH(_smonth,-1),'Table'[Salesperson],_sPerson)
return _previousTitle

Since this is a data preparation activity, I would recommend you to work on Edit Query.

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂