Daily Average Number of Cases Open- Community Services

[zawan]

Dear Members/super contributors, 

In community services we have to report on daily average number of clients in the care program per month. This is calculated under following conditions: 

1. If the client was in care program prior to start of the month and stayed in the care till the end- then we count as number of days in the month. 

2. if the client joined the care program within the month then we take datediff between the date of joining and the end of the month date. 

3. if the Client left the program within the month under consideration then we do the datediff between the start of the that month and the day they left the program. 

We add all these numbers and divide them by the number of days in the month to calculate daily average. 

If someone can help me with syntax of multiple if/ any other function and multiple datediff's or any other way to approach it. 

My data looks like this and the last column is the intended solution 

Thanks in advance 

[vapid128]

IF your date is john date and left date.

I have done similar project

I assume your date look like this, 

add column

rank = RANKX(FILTER('table','table'[client_id]=EARLIER('table'[client_id])),'table'[date],,ASC)

add column

isLast = CALCULATE(MAX('table'[date]),ALLEXCEPT('table','table'[client_id]))=[date]​

add column

nextDate = IF([action]="johned", IF([isLast], TODAY(), LOOKUPVALUE('table'[date],'table'[rank],'table'[rank]+1,'table'[client_id],'table'[client_id]) ), BLANK() )

Create Table

table_new = GENERATE(CALCULATETABLE('table','table'[action]="johned"),GENERATESERIES('table'[date],'table'[nextDate]))

Create Measure

clients = COUNTROWS(table_new)

[zawan]

This is how my data looks like- the highlighted last column shows the intended solution. Thanks heaps for your reply mate. 

[vapid128]

That should be much more easy.

add column

Date Closed 2 = 

If([Date Closed])=0,today(),[Date Closed])

Create Table

table_new = GENERATE('table',GENERATESERIES('table'[Date Created],'table'[Date Closed 2]))

[zawan]

Thank you so much again mate. A little bit of clarity we only take the number of days in a month, I have already created the total length of stay in the program, but for monthly count we take number of days that client was in care this month. For instance the client was in program since 01/01/2022 it means client total length of stay was more than 180 days but when calculating May's averages we consider that as 31 days only.  

[johnt75]

You could try something like

Average days in care =
var totalDaysInCare = SUMX( 'Table',
var startDate = MAXX( { MIN('Date'[Date]), 'Table'[Care start date]), [Value])
var endDate = MINX( { MAX('Date'[Date]), 'Table'[Care end date]), [Value])
return DATEDIFF( startDate, endDate, DAY)
)
return DIVIDE( totalDaysInCare, COUNTROWS('Date') )

This assumes that you have 1 row in your date table for every day.

[zawan]

Thank you so much for your reply and solution Jhont. 
We have a start date and closing date for each client in program. 
For daily count we take the all days of the month if they have been with us from start to the end of the month. For instance if a client joined the program prior to may and stayed till the 2nd of June, in may's daily average calculation we will take 31 days. Similarly if they joined the program during may or left during may then we take the date diff from start or the end date of may to calculate the number of days they were under care in May. 
This is how the data looks like.

Apologies for not being clear enough in the original post! 

[johnt75]

My solution was intended to be put into a visual where the data is grouped by month. If you do that then I think it will work