Calculate the first occurence based on selected period

[mimingiang]

Hi, 

I would like to calculate the first occurence of customer based on period selected. 

Date	CustomerID	EventID
1/1/2023	1	1
1/1/2023	2	2
1/2/2023	3	3
1/2/2023	1	4
1/2/2023	2	5
1/3/2023	1	6
1/3/2023	1	7
1/3/2023	3	8

I want the result to look like this for period from 1/1/2023 - 1/2/2023 

	1/1/2023	1/2/2023	Total
1	1	0	1
2	1	0	1
3	1	1	1
Total	2	1	3

I want the result to look like this for period from 1/2/2023 - 1/3/2023 

	1/2/2023	1/3/2023	Total
1	1	0	1
2	1	0	1
3	1	0	1
Total	3	0	3

[xifeng_L]

Hi @mimingiang ,

You can try below measure.

first occurence of custome = 
VAR SelectedPeriod = ALLSELECTED('Table'[Date])
RETURN
SUMX(
    SUMMARIZE('Table','Table'[CustomerID],'Table'[Date]),
    IF(CALCULATE(MIN('Table'[Date]),SelectedPeriod)='Table'[Date],1)
)

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

[mimingiang]

@xifeng_L Thank you so much for your reply. However, I need to make it work on Month level and it won't work for Month level if I have multiple dates in one month. Do you know how to make it work on month level? 

Date CustomerID 
01/01/2023 A 
01/01/2023 A 
02/01/2023 A 
02/01/2023 C 
01/02/2023 A 
02/02/2023 B 
02/02/2023 C 
02/02/2023 A 
01/03/2023 A 
01/03/2023 B 
01/03/2023 C 

I want the result to look like this for period from 1/1/2023 - 1/2/2023 

	01/2023	02/2023	Total
A	1		1
B		1	1
C	1		1
Total	2	1	3

[xifeng_L · Accepted solution]

Just adjust the measure a little bit.

Demo - Calculate the first occurence based on selected period.pbix

 

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~