Howto count customers per salesperson based on customer log (date)

[CSpina]

Hi there!

I need to create a visual (matrix) to show the number of customers has per salesman and month. I was able to count the amount in the month, but in subsequent months the value becomes null.

I need the a measure to count like that (number is red):

Loga data

1	1	1	2021-01-01
2	2	1	2021-01-01
3	3	1	2021-01-01
4	4	1	2021-01-01
5	5	1	2021-01-01
6	3	1	2021-01-01
7	1	4	202-10-09
8	2	3	2021-11-06
9	5	2	2021-12-10

Any ideas? Thanks in advance.

[v-rzhou-msft]

Hi @CSpina ,

Here I suggest you to create measures to achieve your goal.

Measure = 
CALCULATE(COUNT('Table'[Column2]),FILTER(ALL('Table'),'Table'[ID] = MAX('Table'[ID])&&'Table'[Date]<=MAX('Date'[Date])))

Measure 2 = 
VAR _A = AVERAGEX(FILTER(ALL('Table'),'Table'[ID] = 1),[Measure])
VAR _B = SUMX(FILTER(ALL('Table'),'Table'[ID] <> 1),[Measure])
RETURN
if(MAX('Table'[ID]) = 1, _A -_B,[Measure])

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[celsospina]

Thank you very much! We are almost there, I need a rows total and could you please send thesample pbix?

[v-rzhou-msft · Accepted solution]

Hi @CSpina ,

I suggest you to create a new measure based on [Measure2].

Measure 3 = SUMX(VALUES('Table'[ID]),[Measure 2])

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.