I have two columns that I have added in a table. I want to look up against customer code and determine from separate tables whether an event has happened within the last 30 days.
For the one column, I have entered the below formula and it works perfectly:
Infections in last 30 days = var today = today()
return
calculate(countrows('Infections Raw Data'),
filter( all('Infections Raw Data'[CommencementDate]),
'Infections Raw Data'[CommencementDate] > today - 30 &&
'Infections Raw Data'[CommencementDate] <= today))
However, when I use the same formula in another column but filter out "type of incident", it returns the total count per place rather than per customer.
Falls in last 30 days = var today = today()
return
calculate(countrows('All Resident Incidents Raw Data'), ('All Resident Incidents Raw Data'[Type] = "fall"),
filter( all('All Resident Incidents Raw Data'[DateOfIncident].[Date]),
'All Resident Incidents Raw Data'[DateOfIncident].[Date] > today - 30 &&
'All Resident Incidents Raw Data'[DateOfIncident].[Date] <= today))
How do I get this to update to reflect customer code only rather than count of place.
I also want to do a similar column summing weight change in that date period from another table but can't get the formula to work. Any suggestions would be welcome.
Falls in last 30 days = var today = today()
return
calculate(countrows('All Resident Incidents Raw Data'), ('All Resident Incidents Raw Data'[Type] = "fall"),
filter( all('All Resident Incidents Raw Data'[DateOfIncident].[Date]),
'All Resident Incidents Raw Data'[DateOfIncident].[Date] > today - 30 &&
'All Resident Incidents Raw Data'[DateOfIncident].[Date] <= today))
Thanks!
