How to calculate unique individuals who received services in the last 12 months

[PradeepTh]

Hi, I have a file like this: https://docs.google.com/spreadsheets/d/1jSRtoIMpbkZRpaqGKkLLwk5R4m478IcV_AKzJsr8bX4/edit?usp=sharing...

Column A: Date Column

B: UIC (unitque identifier code of each coustomer)

Column C: Received service (1 = Yes, 0 = No)

I used below DAX to get each month unique customer who reaceived services and that produced correct result.

```

Received services =

CALCULATE ( DISTINCTCOUNT ( Sheet1[UIC]),

FILTER(sheet1,Sheet1[Received service] = 1) )

```

I tried to calculate unique individual who received services in the last 12 months using below DAX but result is showing same as above DAX result

```

received services last 12 months =

CALCULATE([Received services],

DATESINPERIOD(Sheet1[Date],

MAX('Sheet1'[Date]),-12, MONTH))

```

The result should calculate total unique individuals each month calculating 12 months earlier. means Feb 2022 result should include uique individuals from Mar 2021 to Feb 2022.

Pleas suggest.

Regards, Pradeep

[PradeepTh]

Hi,

I tried above DAX and also reviwed all the links but could not get the required result. In my case, the same customer is visiting for same services in different months and so I need to count total number of unique customers who visited in the last 12 months. The 12 months data should be changed based on selection of Months in the slicer.

Regards,

Pradeep

[PradeepTh]

Hi @amitchandak ,

Thank you for proposing the solutions. Let me check and get back.

Regards,

Pradeep

[amitchandak]

@PradeepTh , it seem new formula of customer retenstion

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retenti...

You need measure like

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH))

Rolling 12 before 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],eomonth(MAX('Date'[Date]),-12),-12,MONTH))

The first one current/this period and the second one is the last period

Customer Retention with Dynamic Segmentation, New/Lost/Retain Customer Count: https://www.youtube.com/watch?v=W4EF1f_k6iY