How I accumulate clients different months

[Syndicate_Admin]

Hello Community,
Good day, someone can give me a light with the next problem in power BI.
Example: I have a list of clients in the course of the year where a client can be served more than once in the month and in the year, I need to count the unique new clients as they appear and accumulate them at the end of the year.

DATE	CUSTOMER
ENE-21	KAREN
ENE-21	KAREN
ENE-21	PAOLA
ENE-21	VAIN
21-feb	PAOLA
21-feb	ANDREINA (NOWA)
21-feb	VAIN
21-feb	GABRIELA
mar-21	KARINA
mar-21	PAOLA
mar-21	ANDREINA (NOWA)
mar-21	KAREN
mar-21	ANGELA
Apr-21	CARLA
Apr-21	YENNY
Apr-21	ADELAIDE
Apr-21	PAOLA
May 21	SOFIA
May 21	GIULIANNA
May 21	ANDREINA (NOWA)
June 21-2	PAOLA
June 21-2	ANGELA
June 21-2	ROSE
21-jul	CYNTHIA
21-jul	CYNTHIA
21-jul	PAULA
21-jul	PRISCILLA
21 years ago	KAREN
21 years ago	ANGELA
21 years ago	ANGELA
21 years ago	PAULETTE
UNIQUE PATIENTS	17

Row 3 "Unique patients attended" is the one I need to calculate

	JANUARY	FEBRUARY	MARCH	APRIL	MAY	JUNE	JULY	AUGUST
NUMBER OF ATTENTIONS	4	4	5	4	3	3	4	4
UNIQUE PATIENTS SEEN	3	4	5	4	4	3	3	3
ACCUMULATED SINGLE PTES	3	5	7	10	12	14	16	17

Thank you very much in advance

[PaulDBrown · Accepted solution]

Ok. With this model:

And these measures:

Number of attentions = COUNT(FactTable[CUSTOMER])

Distinct Customers = DISTINCTCOUNT(FactTable[CUSTOMER])

and..

New customers =
CALCULATE (
    DISTINCTCOUNT ( FactTable[CUSTOMER] ),
    FILTER (
        ALL ( 'Date Table' ),
        'Date Table'[Date] <= MAX ( 'Date Table'[Date] )
    )
)

You get the following:

[PaulDBrown]

Do you have a date table in the model?

[Syndicate_Admin]

Hello, if I have a date table, which I have attached to the date field of the list I published

[PaulDBrown]

Do you consider new client when the client first appears in the year? Does the count start at the beginning of each year?

[Syndicate_Admin]

Hi yes, the count restarts every year and is considered a new customer when it appears for the first time in the year.

[PaulDBrown]

Sorry, could you please explain the relevance of the rows where Date = "21 years ago"?

Also, do the date values such as 21-feb or 21-july refer to year-month or date-month?

what do the rows for  Date = June 21-2 actually mean? (does your real data actually look like this??)

It would be very helpful is you provided a sample PBIX file via Dropbox, OneDrive, Google Drive...

[Syndicate_Admin]

Sorry, the table was copied wrong when pasting it, the date field is in date format (DD/MM/YYYY) but pasting it here changes the format.

DATE	CUSTOMER
January/2021	KAREN
January/2021	KAREN
January/2021	PAOLA
January/2021	VAIN
February/2021	PAOLA
February/2021	ANDREINA (NOWA)
February/2021	VAIN
February/2021	GABRIELA
March/2021	KARINA
March/2021	PAOLA
March/2021	ANDREINA (NOWA)
March/2021	KAREN
March/2021	ANGELA
April/2021	CARLA
April/2021	YENNY
April/2021	ADELAIDE
April/2021	PAOLA
May/2021	SOFIA
May/2021	GIULIANNA
May/2021	ANDREINA (NOWA)
June/2021	PAOLA
June/2021	ANGELA
June/2021	ROSE
July/2021	CYNTHIA
July/2021	CYNTHIA
July/2021	PAULA
July/2021	PRISCILLA
August/2021	KAREN
August/2021	ANGELA
August/2021	ANGELA
August/2021	PAULETTE
UNIQUE PATIENTS	17

[PaulDBrown · Accepted solution]

Ok. With this model:

And these measures:

Number of attentions = COUNT(FactTable[CUSTOMER])

Distinct Customers = DISTINCTCOUNT(FactTable[CUSTOMER])

and..

New customers =
CALCULATE (
    DISTINCTCOUNT ( FactTable[CUSTOMER] ),
    FILTER (
        ALL ( 'Date Table' ),
        'Date Table'[Date] <= MAX ( 'Date Table'[Date] )
    )
)

You get the following:

[Syndicate_Admin]

Thank you very much, there is only something that does not come out, it does not restart in each year, it accumulates for all the years that there is customer service.

What would be the modification so that the beginning of each year is restarted?

Thanks a lot

[PaulDBrown · Accepted solution]

This should work:

New customers =
VAR _Year = SELECTEDVALUE ( Date Table [Year] )
CALCULATE (
    DISTINCTCOUNT ( FactTable[CUSTOMER] ),
    FILTER (
        ALL ( 'Date Table' ),
        'Date Table'[Date] <= MAX ( 'Date Table'[Date] )
        && Date Table [Year] = _Year
    )
)