Utilization of Asset by Month

[Blizzardstyx]

Hello All,

I have two tables (asset table with basic information) and an Activity table. My activity table has the asset ID and a pickup date and return date (see example below). I need to find out how many days of a month that my asset was utilized. The issue I'm having are that there are overlapping dates that I do not want to double count. 

Taking the table below let's say I'm looking for days utilized in January. Asset ID 5 should show a total of 3 days (not 4) because of the overlap issue. For Asset ID 10 I should have a total of 18 days. 

Activity ID	Asset ID	Pickup Date	Return Date
738	5	12/25/2019	1/3/2020
799	5	1/3/2020	1/3/2020
538	10	12/15/2019	1/10/2020
809	10	1/9/2020	1/18/2020

My end goal is to have a table like this:

Asset ID	Total days utilized
5	3
10	18

Update: just found out how to add an example PBIX file:

https://drive.google.com/file/d/1lGBKt4dwywIGSP9tKue2RAoypo7alxzJ/view?usp=sharing

Thanks in advance!

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Blizzardstyx]

Hi @Ashish_Mathur ,

Can you use your solution with the data set I provided?

It looks like your date range is a custom list of dates that happens to work with the two examples I provided. I think this solution might have trouble with 100+ activities. 

[Ashish_Mathur]

Hi,

I do not know which dataset you have provided.  Try my solution on your dataset and let me know what problems you face.

[Blizzardstyx]

I don't even know how to implement your solution to my problem.. there's a link in the question to a pbix file. Are you having trouble accessing it?

[richbenmintz]

Hi @Blizzardstyx ,

I created a sample prior to you uploading your file, with what i think is a workable solution:

step 1: create a date table 

Calendar = calendar("2019-01-01", "2020-12-31")

Join Calendar to the pickupdate column in the activity table

create the measure

Days Utilized = 
var minDate = MIN(Activities[PickupDate])
var maxDate = MAX(Activities[ReturnDate])
var minPeriodSelectedDate = MIN('Calendar'[Date])
return 
if(minDate < minPeriodSelectedDate, DATEDIFF(minPeriodSelectedDate , maxDate,DAY)+1,
DATEDIFF(minDate, maxDate, DAY)+1)

 file for you to review

sample pbix download 

hope this helps

Richard

[amitchandak]

Refer to this solution : https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

In the current year calc, you can ignore blank handling and you have date diff between start and max and max and end added up

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin

[Blizzardstyx]

I have an example PBIX file, how do I add? 😕

[richbenmintz]

@Blizzardstyx 

you can save to onedrive or google drive and share a link to the file

[richbenmintz]

Hi @Blizzardstyx 

I have updated your file with a measure they I believe provides you with what you need

Days Utilized = 
var minDate = MIN(Activities[Pickup Date])
var allmaxDate = CALCULATE(MAX(Activities[Return Date]), ALLEXCEPT(Activities,'Activities'[Tractor]))
var maxDate = max('Activities'[Return Date])
var minPeriodSelectedDate = MIN('Calendar'[Date])
var maxPeriodSelectedDate = MAX('Calendar'[Date])
return 
SWITCH(TRUE(), ISBLANK(minDate), BLANK(),
minDate < minPeriodSelectedDate && allmaxDate > maxPeriodSelectedDate, DATEDIFF([minPeriodSelectedDate], maxPeriodSelectedDate,DAY)+1,
minDate < minPeriodSelectedDate, DATEDIFF(minPeriodSelectedDate, maxDate,DAY)+1,
maxDate > maxPeriodSelectedDate, DATEDIFF([minDate], maxPeriodSelectedDate,DAY)+1,
DATEDIFF(minDate, maxDate, DAY)+1)

basically find following variables:

minDate: the minimum pickup date of the assest that was dropped off in the selected period

allmaxDate: the maximun drop off date of an asset dropped of in the selected period

maxDate: maximum return date of the asset that was returned in the selected period

minPeriodSelectedDate: the min date of the selected period

maxPeriodSelectedDate: the max date of the selected period

then conditionally determine the date difference based on whether

- the item was borrowed in a prior per, returned this period, borrowed again and will be returned in the future

- the item was borrowed in a prior period and returned this period

- the item was borrowed this period and will be returned in a future period

- the item was borrowed and returned in this period

One case that i have not captured are assests borrowed in prior periods and not returned in the current period, however i will leave that challenge to you 😁

link to your sample file with my mods 

[Blizzardstyx]

@Rich 

Thanks for the thorough reply!

I think something is a little off though.

I filtered for a Asset ID 1676 and it doesn't look right. It only has two activities yet it's showing 30 days of utilization?

1/2/2020 -> 1/9/2020

1/30/2020 -> 1/31/2020

[richbenmintz · Accepted solution]

Hi @Blizzardstyx ,

Ok so I have re thought this as come up with another way to solve this problem. New solution creates a new fact table called activities_filled with the following DAX (Table Tools -> New Table)

Activities_filled = SUMMARIZECOLUMNS(Activities[Tractor], 'Calendar'[Date],FILTER(CROSSJOIN('Calendar', Activities), 'Calendar'[Date] >='Activities'[Pickup Date].[Date] && 'Calendar'[Date] <= 'Activities'[Return Date]))

the formula performs the following:

- cross join calendar and activities table

- Filter where date >= pick up date && date <= return date

- return the distinct tractors and dates (removes the scenario where the pickup dates are the same)

 I can then join this to calendar and tractor to get utilization by tractor and date

I have saved to the same location

[richbenmintz]

Hi @Blizzardstyx ,

I will have a look tomorrow, My thought is that the item has a date returned in a future month, will review the though and try to fix it

[richbenmintz]

@Blizzardstyx , 

I see the issue, need to deal with non contigous date ranges within a period, ie break between check in and next check out, in a meeting all day, will review and fix as soon as i can