Counting active days between start and end date and slicer min max dates

[Kepler1571]

Hi All, I have been stuck on this issue for way too long so reaching out for some support. 

I have a table that shows a client's name, location, start date and end date. 

Between the clients start and end date they client will be considered as active. 

I need to build a report that calculates the number of days a client is active (datediff) but it also needs to be responsive to a minimum and maximum date range as selected by a slicer. E.g a client active from 01/01/2022 to 31/12/22. Slicer selected equals 01/08/2022 to 31/08/2022 therefore the result should be that the client is active for 31 days between that date range. and then sum of all clients to get total number of active days for all clients. 

i have my slicer run by dates in a date table and through several attemps have got the closest with the following custom measure below. 

In this case when i place the measure in a table i get the correct result for each individual client. However the total is not adding up the result for each individual client rather it is producing a result based on the minimum and maximum dates as set in the slicer. image attached should show total 30. Any help would be greatly appreciated. 

Bed night count = 
VAR MinDate = MIN('Dates'[Date])

VAR MaxDate = MAX('Dates'[Date])

VAR MinCalcDate = IF(SELECTEDVALUE('Client living arrangements'[Start Date]) <= MinDate, MinDate, IF(SELECTEDVALUE('Client living arrangements'[Start Date]) > MaxDate, BLANK(), SELECTEDVALUE('Client living arrangements'[Start Date])))

VAR MaxCalcDate = IF(SELECTEDVALUE('Client living arrangements'[End Date])>= MaxDate || SELECTEDVALUE('Client living arrangements'[End Date]) = BLANK(), MaxDate, IF(SELECTEDVALUE('Client living arrangements'[End Date])<MinDate, BLANK(), SELECTEDVALUE('Client living arrangements'[End Date])))

RETURN

DATEDIFF(MinCalcDate,MaxCalcDate,DAY)

 

[srutiR2403]

Hi Kepler

I am also stuck in calculating sum of the active days can you please post the steps how u achieved it.

[amitchandak]

@Kepler1571 , Try a measure like

New measure =
var _max = maxx(allselected(Date),Date[Date])
var _min = minx(allselected(Date),Date[Date])
return
Sumx(Table, max([Start Date], _min ), min([End Date],_max), Day)

[Kepler1571]

Hi, when trying this i get the error below:

New measure =
var _max = maxx(allselected(Dates),Dates[Date])
var _min = minx(allselected(Dates),Dates[Date])
return
Sumx('Client living arrangements', max([Start Date], _min), min('Client living arrangements'[End Date],_max),Day)

Too many arguments were passed to the SUMX function. The maximum argument count for the function is 2.

[Ashish_Mathur]

Hi,

Share data in a fornat that can be pasted in an MS Excel file.

[Kepler1571]

here is some sample data 

client	Status	Name	Start Date	End Date
35207	Inactive	house 1	12/12/2021 0:00
53840	Active	house 2	21/12/2020 0:00	28/01/2021 0:00
53840	Active	house 2	28/01/2021 0:00	12/02/2021 0:00
53840	Active	house 3	12/02/2021 0:00	12/10/2022 0:00
53840	Active	house 4	12/10/2022 0:00
53840	Active	house 1	29/10/2020 0:00	21/12/2020 0:00
53841	Active	house 4	7/02/2023 0:00
53841	Active	house 6	26/03/2021 0:00	16/12/2021 0:00
53841	Active	house 3	15/09/2020 0:00	26/03/2021 0:00

[Ashish_Mathur]

Hi,

Please find attached the PBi file.

Hope this helps.

[Kepler1571]

Hello, 

This just counts the active clients. I'm trying to calculate days that each client is active for and then the sum of all the active days.

[Ashish_Mathur]

Hi,

Writ this measure

=Active days = CALCULATE(COUNTROWS(Data),Data[Status]="Active")

Hope this helps.

[Kepler1571]

ok i see what you've done now, you have created an entry for every date for every client and marked if they are active on that day or not. How did you do this? The sample i sent only has three clients but my actual data set has 100s and more coming every day? Is there a way to automate this? 

[Ashish_Mathur · Accepted solution]

See the transformation steps in the Query Editor.

[srutiR2403]

Hi Ashish

I have similar requirement but little different

In this example i could create measures for duration and active days.But I could not sum the active days. In the above example 820/365=2.245. I cannot get the value of 820. 365 comes from the date slicer that we can calculate.

For duration i added a column in the table and did datediff.

For active days i creasted a measure and i could get the activedays count for each pol.but For summing up of the active days i am not sure how to achieve.

[Ashish_Mathur]

Hi,

I am clueless about that you want.  Start a new thread, explain the question and show the expected result there.

[srutiR2403]

Thank you ashish for replying. I have completed this sissue