Number of days per year over the service life of a piece of equipment?

[jspotts37215]

I'm analyzing the utilization of a fleet of rental equipment. I know the purchase date for each item so it's easy enough to calculate the overall number of days for which an item would theoretically be available to rent. However, the end result I need is a row per year per item containing the number of days in that year for which the item would be available to rent.

Anyone have any good ideas on how to approach this transformation?

[HarrisMalik]

@jspotts37215 Can you give us some  sample data and desired output?

[jspotts37215]

Here's a simplified view of sample data.

"Base" data:

ItemCode | SerialNumber | PurchaseDate

XYZ421    | 121204           | 07/01/2013

PRQ574   | 242408           | 12/01/2014

Desired output (ignore the actual numbers--just did this of the top of my head):

Year  | ItemCode | DaysAvailable

2013 | 121204     | 183

2014 | 121204     | 365

2015 | 121204     | 343

2014 | 242408     | 31

2015 | 242408     | 343

[leonardmurphy]

I'll see if I can walk you through a solution (I don't claim that there isn't a better way though).

First, I need a date table related to PurchaseDate. The date table should consist of every date in every year that I care about with no gaps. I have DimDate with 2 columns: Date and CalendarYear.

In a fantastic example of creative naming, I've called your sample data table Sheet1.

The first two things I want to calculate is the first & last of each year. These are simple measures:

MinDate = MIN(DimDate[Date]) 

MaxDate = MAX(DimDate[Date]) 

In a table visualization, if you slice MinDate and MaxDate by CalendarYear, it will always show the first and last date of each year.

Next, I want to calculate the purchase date of the item, regardless of what year I'm slicing by in my visualization. 

EarliestPurchaseDate = CALCULATE(MIN(Sheet1[PurchaseDate]), ALL(DimDate))

In a table visualization sliced by serial number and by calendar year, this will always show the purchase date of the item. (If you don't slice by serial number, it will only show the purchase date of the first item purchased).

Next, for any given year there are 3 possibilities:

1. The item was purchased before the start of the year, in which case we count days from 1/1

2. The item was purchased during the year, in which case we count days from the purchase date.

3. The item was purchased after the end of the year, in which case we want to ignore the year entirely

4. The year begins in the future, in which case we want to ignore the year entirely (that way you can have 2016+ in your date table without issue)

Similar logic applies to the end of the year, except we care about today's date rather than the purchase date.

So I create 2 more measures to calculate the start & end date I need to count:

EarliestDateCalculated = IF([EarliestPurchaseDate] > [MaxDate] || [MinDate]-1 > TODAY(), BLANK(), IF([EarliestPurchaseDate] < [MinDate], [MinDate]-1, [EarliestPurchaseDate]))

LatestDateCalculated = IF([MinDate] > TODAY() || [EarliestDateCalculated] < [MinDate]-1, BLANK(), IF([MaxDate] < TODAY(), [MaxDate], TODAY()-1))

The -1 tweak is because if you do a difference between two dates, the starting date isn't counted. Since we want to count 1/1 of each year, I subtract 1 from the minimum date. I'm sure there's a cleaner way of doing this.

Now, if you slice the above two measures by serial number & year, you'll get the first date to count from and the last date to count from in any given year. Years that are irrelevant for a given item are excluded.

From here, it's a simple DATEDIFF:

AvailableDays = IF(ISBLANK([EarliestDateCalculated]), BLANK(), DATEDIFF([EarliestDateCalculated], [LatestDateCalculated], DAY))

Obviously this could be one giant measure, but the building block measures make it easy to solve a problem like this (and easy to troubleshoot if a number is calculating wrong). You can hide the interim measures from the report view once you're done, so they don't confuse end-users.

[jspotts37215]

For starters, thanks for a thorough description of a solution. It's given me a couple of ideas, most important of which is the need to   add a Date Dimension table. I should have been clearer about why I want to transform my data into the output form shown in my sample. Once I know how many days per year that a unit has been in service -- for each year of its service life to-date -- I then need to aggregate the available days per year for each unit of a given type (i.e., all serial numbers for a given item code). I need this aggregated value for subsequent calculations.

[leonardmurphy]

Hmmm...

The problem with my above solution then is my use of MIN. If you select 2 serial numbers in my solution, you'll just get the available days from the earliest purchase date of the two, whereas you want to sum the available days from both serial numbers together. Not unreasonable. It means 12/1/2015 gets counted as 2 days if 2 products were purchased prior to that date.

The best solution is probably going to be in Power Query...using Power Query to expand a given product into a row for each year for that product. And calculating the available days for that serial number/year at the same time. You would then relate that table to the serial number & date table you currently have. Unfortunately my Power Query skills aren't good enough to be able to explain how to do that. 

If your source data is SQL-based, you could also write a SQL statement to generate such a fact table (a cross join of serial number and year where the year is after the purchase date for the serial number and before the current year).

Getting your data model right in this way will simplify what you need to do in DAX greatly (and allow you to do things like slice & dice available days by unit type easily)

[jspotts37215]

Unfortunately this data source is a flat file so I guess I need to take the Power Query plunge and see if this kind of transformation is possible. Thanks for your willingness to help!

[HarrisMalik]

Hi @jspotts37215

As @leonardmurphy said these type of requirements are better handled in the ETL and Modeling. In you case if you do not have a lot of flexibility thee can be some quick ways to report it. You can also try following solution (it is not a pretty solution though)

Steps by step:

1. Suppose you imported you data in a table called Base having columns ItemCode, SerialNumber and PurchaseDate

2. You can create a date table on run time having all the last days of year (As you want to report end of year) and today. This table then will be crossjoined with your Base table to get all the possible EndDates. In PBI Desktop from left ribbon go to Data tools (Middle icon) and Add a New table with following expression:

JoinedTable = CROSSJOIN(FILTER(CALENDAR("2010-01-01",TODAY()),OR(AND(MONTH([Date])=12,DAY([Date])=31),[Date]=TODAY())),Base)

This will create a new table called JoinedTable.

3. Rename the column Date to EndDate

4. Now create a reporting table with following expression

ReportingTable= FILTER(JoinedTable,JoinedTable[EndDate] > JoinedTable[PurchaseDate]) 

5. In Reporting table add new calculated column

StartDate = IF(YEAR(ReportingTable[PurchaseDate])=year(ReportingTable[EndDate]), ReportingTable[PurchaseDate],DATE(YEAR(ReportingTable[EndDate]),1,1))

6. Add a last calculated column

AvailabilityInDays = DATEDIFF(ReportingTable[StartDate],ReportingTable[EndDate],DAY)

This way you have for each product availability per year.

As I mentioned above it is not an elegant solution but a quick win 🙂

Cheers

Harris

[jspotts37215 · Accepted solution]

Thanks for the excellent ideas. These put me on the right path. I realized that I needed to get the data model right first, and that meant implementing your solution in Power Query Formula Language, AKA "M".

Here's what I did:

1. Created a DimDate table using Matt Masson's oft-cited solution.

2. Created a 'YearEndDates' table derived from DimDate, like this:

let
    Source = DimDate,
    #"Removed Columns" = Table.RemoveColumns(Source,{"QuarterOfYear", "MonthOfYear", "DayOfMonth", "DateInt", "MonthName", "MonthInCalendar", "QuarterInCalendar", "DayInWeek", "DayOfWeekName", "WeekEnding"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Year"}, {{"End Date", each List.Max([Date]), type date}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each 1)
in
    #"Added Custom"

Note that the "Custom" column with all values set to 1 is part of a trick needed to do a cross join in a subsequent step.

3. Added a similar "Custom" column with all values set to 1 in my source data table.

4. Cross joined YearEndDates with my source table, like this:

= Table.NestedJoin(#"Added Custom2",{"Custom"},YearEndDates,{"Custom"},"NewColumn",JoinKind.FullOuter)
= Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"End Date"}, {"End Date"})

Now I had a row per year for each unique product for every year in my YearEndDates table.

5. Filter out all the rows for years outside the service life of each product:

= Table.SelectRows(#"Expanded NewColumn", each Date.Year([End Date]) >= Date.Year([PurchaseDate]) and Date.Year([End Date]) <= Date.Year(DateTime.LocalNow()))

6. Set the correct End Date for the current year:

= Table.ReplaceValue(#"Filtered Rows3",Date.EndOfYear(DateTime.Date(DateTime.LocalNow())),DateTime.Date(DateTime.LocalNow()),Replacer.ReplaceValue,{"End Date"})

7. Add a new Start Date column and set its value accordingly -- Purchase Date if it's the first year of service or Jan 1 if thereafter:

= Table.AddColumn(#"Replaced Value", "Start Date", each if Date.Year([PurchaseDate]) = Date.Year([End Date]) then [PurchaseDate] else Date.StartOfYear([End Date]))

8. Last step is to add a new AvailabilityInDays column using the Power Query approach to datediff's:

= Table.AddColumn(#"Reordered Columns", "AvailabilityInDays", each Duration.Days([End Date] - [Start Date]))

Thanks again to @leonardmurphy and @HarrisMalik for putting me on the right track!