Calculate count rows with all except and filter

[NikkiSavage]

I have a data set and I am trying to calculate the number of instances of infections in the past 2 weeks by patient. I am using the below formula:

Infections in last 14 days = var today = today()

return

calculate(COUNTROWS('Infections Raw Data'),

allexcept('Infections Raw Data', 'Infections Raw Data'[Customer_Code]),

filter( all('Infections Raw Data'[CommencementDate].[Date]),

'Infections Raw Data'[CommencementDate].[Date] > today - 14 &&

'Infections Raw Data'[CommencementDate].[Date] <= today))

However, whilst this returns a 1 for every infection in the past 14 days, it also picks up other infections that a customer has had even if it is months ago. E.g. if patient ABC had an infection in January as well as in the last 2 weeks, it will state that there has just been 1 infection in the past 14 days for this customer which is correct. But because there is another line in the data set detailing the infection in January for that customer, a 1 is also returned for this line which I don't want.

I essentially just want to count the number of rows per patient for the last 14 days. E.g. If a patient has had 2 infections in the past 14 days, I want a 2 in each line detailing the infection (there is one line per infection) or if a patient has only had one infection in the past 14 days but one months ago, I want it to return a 1 just in the row for the infection in the past 14 days.

I have signed an NDA so cannot post data for confidentiality reasons. Hopefully the above explanation is sufficient for help.

Thanks.

[amitchandak]

@NikkiSavage , Try like

Infections in last 14 days = var today = today()
return
calculate(COUNTROWS('Infections Raw Data'),
filter( all('Infections Raw Data'),
'Infections Raw Data'[CommencementDate] > today - 14 && 'Infections Raw Data'[CommencementDate] <= now() && 'Infections Raw Data'[Customer_Code] = max('Infections Raw Data'[Customer_Code]) ))

This on the basis of customer

[NikkiSavage · Accepted solution]

Hi. Thanks for your reply. That just returns blank data so doesn't work. All good though as the client has now decided they don't need that for now. Thanks anyway.