Search code examples
powerbidaxm

How to show running totals of time defined by an interval and time per instance

I have a table defining an interval for when a service to a client will be performed. Also defined is the alotted duration of the service everytime it is performed.

Like this:

enter image description here

The above table is an example of services that will be executed for some clients.

For ClientId:1 the information should be read as:

Client 1 has ordered ServiceType:100 to be performed for 1.5hrs every third day, starting 2020-01-01 and until further notice.

For Client:4 the information should be read as:

Client 4 has ordered ServiceType:200 to be performed for 4 hours every 4th day, starting 2020-01-02 and until further notice.

My boss now wants me to produce a diagram showing the total hours spent, for any service, by date.

A date range between 2020-01-01 and 2020-01-31 should create the following theoretical dataset to display:

(I only need to display Date and Total time, the other columns can be ignored)

enter image description here

The data above is then to be shown in a diagram, like this:

enter image description here

My issue is how to create a measure that can compute total time, given the currently selected date filter in the report and that can be used to create the above diagram.

Any help is much appreciated!

Kind regards, Peter

Solution

  • You can achieve this using M (Power Query), my solution does not manage all the details you ask for like the "IntervalType", but allows you to obtain the rows you need once the input is standardized.

    The below M script will work with the following requirements (you can view the full script using the advanced editor):

    • All the rows and used metrics share the same unit of measure ie: Days
    {here you have already loaded your table}
/*Calculate the difference between the 2 dates in days*/
/*note that if the end date is 9999-12-31 you may want to set an upper limit*/
#"Added Custom" = Table.AddColumn(#"Changed Type", "DaysInterval", each Duration.Days([EndDate]- [StartDate])),

/*Calculate how many times the service will be performed in the calculated interval*/
#"Added Custom1" = Table.AddColumn(#"Added Custom", "IntervalOccurence", each Number.RoundDown([DaysInterval]/[Interval])),

/*Use what has been calculated so far to create a list of dates for each row*/
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "ListDates", each List.Dates([StartDate],[IntervalOccurence],#duration([Interval], 0, 0, 0))),

/*Expand the list to rows (using the UI click on the arrows in the column header)*/
#"Expanded ListDates" = Table.ExpandListColumn(#"Added Custom2", "ListDates")

    Afterward, you will need only to remove the columns you don't need.