Count Number of Days an Equipment is at a location.

[Tremor1981]

Hello - I've tried to come up with a dax that will count the number of days an equipment is at a location. I've included a data sample below: The end goal would be to have: Resource Description, Location, Date Range and number of days.

[uzeq]

I don't know if my solution is very efficient. Here's what I did in the same situation in the Power Query Editor: I made a custom column with this formula:
Duration.Days([Date Finished] - [Date Started])+1

In my situation, I added a +1 because my equipment being the same place logging as 0 days was causing a nuisance.

[Tremor1981]

I think that would work if the Start date/Finish date was align but in this case the finished date is only showed when the equipment piece is moved(see spreadsheet)

[uzeq]

You can also add conditional column - if empty, use the Today function. If populated, use what is in there.

https://learn.microsoft.com/en-us/dax/today-function-dax

Then use that for your duration calculation.

Again - not sure if this is the most efficient solution but it has worked for me.

[Tremor1981]

I don't understand

[uzeq]

From what I understand, your finish date is either empty or not populated. If you have a finish date, you can use power query to add a custom column to count the days, using Duration.Days.

Since you have gaps, you could make an additional column. If you have a finish date, pull that in. Otherwise, use the current date in your new column. Use this new column for the subtraction mentioned above. This would give you an ongoing count of duration for the equipment.  

[Tremor1981]

I see. I didnt explain fully. So each row represents a equipment move. the column finish date is worthless(it's how the systems works right now). The move from column and move to column lets me know that a equipment peices was moved. So you'll have to take the difference of the start dates. see below