Determining assignments by hour for each asset in period

[PotatoGlunn]

Good morning Microsoft Dataverse Power Apps Community,

I'm hoping to fill in some knowledge gaps that need filling-in in order to complete a new dataflow into Power BI. I will keep this as short as possible.

We are trying to build a Power BI dashboard that will show the average number of daily miles a Fleet's assets drove in a month. In order to get the most accurate number, I need to be able to pull the total amount of time each asset spent in each Fleet in each month - including the time the asset is sitting around doing nothing.

So. We have a table AssetProfileHistory. This table acts as an audit table when changes are made to the assignment of an asset from one group to another. These four fields are what we care about:

• AssetID
• OldFleetID
• NewFleetID
• DateOfChange

For instance, if asset A10 is moved from Group B to Group D on 12/27/20 01:00, a row would be generated with values:

Asset ID = A10

OldAssignmentID = B

NewAssignmentID = D

DatetimeOfChange = 12/27/2020 01:00

I envision that I need a calendar table granular to the hour. Each asset would need a corresponding entry for every hour, and in that hour, I need to know what fleet it was on. From there I can join it to the miles table and allocate miles to the appropriate fleet based on the hour they were completed.

I am going to start playing with it now but am pretty sure I'm going to get stuck. I know how to make joins happen when an AssetProfileHistory fleet change is made, it's the hours between changes that I don't know how to stamp with the current fleet. Any help is much appreciated.

[jlindstrom]

Mr @PotatoGlunn , There are a couple of ways that you can do this--Here is one of them.

On your audit record, update the value when it enters that phase and when it leaves the phase---IOW, instead of just creating a record when the assignment ID changes, capture on the same audit record when it leaves. One way I've done this is to have a "shadow" field on the entity to capture the previous value.

So on your asset you have a fleet lookup field. Add the following fields to your asset:

• previous fleet
• previous fleet date

Then, on change of the fleet, write the following values to the audit record:

• Previous fleet
• previous fleet date
• Current fleet
• current date

Then update the asset and make the current fleet date the previous fleet and the current date the previous fleet date.

This is a great use for real time workflows as they can execute "before" a change happens, so they are good for snapshotting the prior value.

By storing both the previous and current values on your audit record, you can use a standard calculated field to calculate the difference in the start and end date of each stage..

[PotatoGlunn · Accepted solution]

Thanks @jlindstrom. I had to go another route due to some DB limitations on my end (can't add new fields to this asset table), but you got me thinking in the right track.

I ended up going back a bit and building a new column in PowerQuery with: {Number.From([DateStart])..Number.From([DateEnd] - #duration(1, 0, 0, 0))}, then expanding that out to get my "one row per Asset + Date pair". From there I join each date against the dates of work to allocate accordingly.