Powerpivot data that spans over multiple filter values

Question

I have outage information stored in Excel Powerpivot. I am trying to use Powerpivot to display and calculate uptime availability per month, however, I am a bit stuck with outages that spans over two months.

My current setup:

Outage table has four columns: Application, Outage Start Time, Outage End Time, Duration. Duration is a calculated column that is the difference between the end time and start time.

The Outage Start Time is connected to a date table. I have slicers per month, so users can select the month they like to see availablity data, and powerpivot table will show availability for that month for different applications.

If the outage start time and end time falls within the same month, then availablity calculation is correct. However, for example, if the outage starts at the end of July and ends at the beginning of August, then this is only considered to be an outage for July (because of how the start time is tied to the date table). Ideally, I would like to see the duration of this outage split up over both July and August. Is this possible?

Thanks!

Jacob Jacob · Accepted Answer · 2013-03-26T11:09:40

Tallie,

It's absolutely possible! I solved it by creating 2 calculated columns on the Outage Table:

One to determine the duration of the outage in the current month:

=if([End]>EOMONTH([Start],0),EOMONTH([Start],0)-[Start],[End]-[Start])

And another to determine the outage in the following month:

=if([End]>EOMONTH([Start],0),[end]-EOMONTH([Start],0),blank())

It then becomes the slightly more complex where I want to create a measure that not only sums the current month outage but also the following month outage from the previous month. I began by creating a measure that sums each of the 2 calculated columns respectively - this is good practice as it helps a logical build up and improves performance:

[Cur Month Dur] = sum(Outage[Cur Month Outage])

[Following Month Dur] = sum(Outage[Following Month Outage])

I then add to the [Cur Month Dur] a filtered version of the [Following Month Dur] which opens the filter context of the measure and looks in the date table for the previous month (must be numeric):

=[Cur Month Dur] + 
      CALCULATE([Following Month Dur],
                    FILTER(
                         ALL(dimDate),
                         dimDate[Month Number]=max(dimDate[Month Number])-1
                          )
                )

You can find a detailed explanation of the method here.

I uploaded my workings to SkyDrive which you might find useful.

HTH

Jacob

Powerpivot data that spans over multiple filter values

1 Answers