Calculating whether an asset has been in service in a particular time period

[jhambach]

I'm new-ish to Power Query and Power BI. Have done a few basic things but struggling to think about how to begin on the next item on my list.

My company has machines that are at customer sites, and I need a better way to create two metrics per time period.

1. Count of the assets that were "in service" during the time period (week/quarter/month)?
2. How many in service "runtime hours" did the asset accumulate during the same time period as #1?

My source data is in Salesforce Service Cloud, and for purposes of this analysis the calculations I'm trying to do are from the Asset object. If an asset has gone in service it will have a date in the "Accepted Date" column, and if it has been retired it will have a date in the "Uninstall Date" column. The data looks something like this:

Asset Serial Number	Status	Accepted Date	Uninstall Date
1234	Accepted	1/22/2021
2235	Accepted	3/4/2022
4333	Obsolete/Returned	6/5/2020	3/31/2022

Ideally I'd like to either create two data tables in Power Query that refresh weekly with the serial number (e.g. unique ID) , each having and a value per week the metric it contains (In Service = 1 or 0, Runtime). Or do those calculations in Power BI via DAX expressions.

I'll be using those metrics in Power BI visulaizations for things like assets in service, using runtime for downstream calculations, etc. Any advice on how to approach? Thanks!

[jbwtp · Accepted solution]

Hi @jhambach,

First you need a Calendar table, which can be created from your Assets table like this:

let
    Source = List.Transform({Number.From(List.Min(Assets[Accepted Date] & Assets[Uninstall Date]))..Number.From(Date.From(DateTime.LocalNow()))}, each Date.From(_)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Date", type date}})
in
    #"Changed Type"

You can then use this table as a filter (do not build relationships to the Assets table, though).

The measure for the asset count would be something like this:

CountOfAssets = COUNTROWS(FILTER(Assets, Assets[Accepted Date]<MAXX('Calendar', [Date]) && (Assets[Uninstall Date] > minx('Calendar', [Date]) || ISBLANK(Assets[Uninstall Date]) )))

The measure for the hour count is more complex:

Hours = SUMX(SUMMARIZE(Assets, [Asset Serial Number], "Hrs", max(datediff(max(MAXX(Assets, [Accepted Date]), MINX('Calendar', [Date])), min( if(ISBLANK(MINX(Assets, [Uninstall Date])), TODAY(), MINX(Assets, [Uninstall Date])), MAXX('Calendar', [Date])), HOUR),0)), [Hrs])

Kind regrads,

John

P.S. Adding an example of the output:

[jhambach]

Thank you so much! With a few easy tweaks this all worked really well, and I learned something. Appreciate the help.