Calculate how many days spent in a status, help find average

[dostdahl]

Hello,

This might be more of a two part question, but I need a way to find out how many days a record has spent in a status and then I'd like to find the average amount of days that are being spent in each status. The biggest issue I'm running into is starting a new count of days if a status has been updated.

Part 1:

I have an Excel spreadsheet that has data including a unique ID, Name, Status, and Date Exported (which is the date we manually pull in information.) Example:

Unique_ID	Name	Status	Date Exported
1	Bill Smith	Research	2/19/2021
1	Bill Smith	Research	5/26/2021
1	Bill Smith	Research	7/6/2021
2	Bob Jones	Research	2/19/2021
2	Bob Jones	Research	5/26/2021
2	Bob Jones	Cultivation	7/6/2021
2	Bob Jones	Solicitation	9/17/2021
3	Mike Miller	Research	5/26/2021
3	Mike Miller	Cultivation	7/6/2021
3	Mike Miller	Stewardship	9/17/2021
4	John Doe	Solicitation	9/17/2021
4	John Doe	Stewardship	10/1/2021

It's possible for a record to stay in the same status for each data pull (like ID #1), but there are cases where a record's status has been updated.

I've tried creating some measures to help calculate the amount of days that have passed between exports. It seems to work for calculating a running total of days in a status, but it's not accounting for the times where there is a status change and a new count needs to start. In the first measure, I've hardcoded the last export date which isn't probably the most efficient way to do it.

DaysInSinceExport = DATEVALUE("10/01/2021")-Table[Date Exported]

This measure does a good job of calculating the total number of days in a status by specific ID number, but doesn't factor in and status changes.

DaysStatus = CALCULATE(
                SUM(Table[DaysInSinceExport]),
                FILTER(
                    Table,Table[Date Exported] = CALCULATE(FIRSTDATE(Table[Date Exported]), ALLEXCEPT(Table,Table[Unique_ID],Table[Status]))))

I'm a bit new to DAX and writing measures but I'm sure there's a way to solve this. 

Part 2:

Once I'm able to calculate the amount of days a record has spent in a status for the entire dataset of records, I'd like to find the average of how many days are spent in each Status.

Any help or suggestions will be greatly appreicated, please let me know if you need additional information, thank you in advance!

[dostdahl]

Hi @Vera_33, apologies for the delayed response. Regarding expected results, I’d like to be able to calculate the Average for days spent in each of the 4 statuses. Additionally, I have another column that lists a Relationship Manager (RM) for the distinct ID. I’d like to filter by RM to see the averages for time spent in the 4 statuses for their group of records too.

When I try the measure Avg = AVERAGE(Table[DaysInSinceExport]) it doesn’t seem to be calculating correctly. I’ve tried using the DaysInSinceExport column in various calculated measures but isn’t an option to use.

The table of data looks something like this:

ID	Name	Status	Relationship Manager	Date Exported
1	Bill Smith	Research	RM1	2/19/21
1	Bill Smith	Research	RM1	5/26/21
1	Bill Smith	Research	RM1	7/6/21
2	Bob Jones	Research	RM2	2/19/21
2	Bob Jones	Research	RM2	5/26/21
2	Bob Jones	Cultivation	RM2	7/6/21
2	Bob Jones	Solicitation	RM2	9/17/21
3	Mike Miller	Research	RM1	5/26/21
3	Mike Miller	Cultivation	RM1	7/6/21
3	Mike Miller	Stewardship	RM1	9/17/21
4	John Doe	Solicitation	RM3	9/17/21
4	John Doe	Stewardship	RM3	10/1/21

To display this, I’d like to use the Card visual to show Average amount of days for each status. And then, be able to select a Relationship Manager from a Slicer and have the totals update based on the Relationship Manager selected. Hope this helps clarify, but let me know if you have other questions, thank you in advance.

[Vera_33]

Hi @dostdahl 

Just messaged you my email, hope you can send me a sample file and expected results, so I can work from there

[v-xiaotang]

Hi @dostdahl 

You can try this,

create the measures,

TotalDays = 
    var _mindate=CALCULATE(MIN('Table'[Date Exported]),ALLEXCEPT('Table','Table'[Unique_ID]))
    var _maxdate=CALCULATE(MAX('Table'[Date Exported]),ALLEXCEPT('Table','Table'[Unique_ID]))
return DATEDIFF(_mindate,_maxdate,DAY)

Average = 
    var _countStatus= CALCULATE(DISTINCTCOUNT('Table'[Status]),ALLEXCEPT('Table','Table'[Unique_ID]))
return [TotalDays]/_countStatus

result

If this doesn't meet your needs, please add more information.

(1) what's your TotalDays? 

(2) what's the average do you want? I mean, for example, the average value is x1 divided by x2 . so what's the x1? x2? 

Thanks

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[Vera_33 · Accepted solution]

Hi @dostdahl 

Can you provide the expected results? Take ID=4 as an example, the days between the two status "Solicitation" and "Stewardship" should be counted for Solicitation, right? Not sure why there is a hardcoded date 10/01/2021...

it is a Sum of [DaysInSinceExport]

DaysInSinceExport is a DAX Calculated column

DaysInSinceExport = 
VAR CurDate = Table2[Date Exported]
VAR CurID=Table2[Unique_ID]
VAR NextDate = MINX(FILTER(Table2,Table2[Date Exported]>CurDate&&[Unique_ID]=CurID),[Date Exported])
RETURN
DATEDIFF(CurDate,NextDate,DAY)

[dostdahl]

Hi @Vera_33 - Thank you so much for your reply. The calculated column you provided seems to be counting the days that a record is in a status correctly. Would you happen to have a suggestion as to how I can calculate the Average for how many days are spent in each status? Since DaysInSinceExport is a calculated column, I'm having an issue using it to calculate the Average. 

[Vera_33]

Hi @dostdahl 

What are the expected results?