measure to selectively sum durations of events

[smaur]

Just starting out here with powerpivot, and I need to do something that seems a bit tricky to me.

I'm setting up a data model to report on performance of a fleet of power generators. One of my main data tables, I'll call it ProductionStop, records 'outage' events, so a typical record will identify the specific equipment by serial number [unitSN], it will include two dates, the first being the date and time the unit went off line, call it [dateOffline], the second date records when the unit returned to service, lets call it [dateOnline]. These dates are in format mm/dd/yyyy hh:mm:ss AM/PM. I also have a date lookup table currently formatted as mm/dd/yyyy.

I need a measure that will totalize the durations (hours) of all outages that have occurred within a specified time frame for any specific [unitSN].

The measure, lets call it [duration], needs to count all the hours for outage events whose start and stop times fall fully within a user selected Report time frame,
...but for the outages that have either commenced prior to beginning of the Report time frame as well as those who have continued on past the end of the Report time frame, the measure should only count the down hours that actually fell within the requested Report time frame.

for example, say 'engine1' has two outage events as follows:

unitSN --------- dateOffline ----------------------- dateOnline
engine1 ------- 12/10/2015 12:00:00 AM -------- 1/10/2016 12:00:00 AM
engine1 ------- 1/20/2016 12:00:00 AM --------- 1/21/2016 12:00:00 AM

Now, while the total duration of the two outages is the sum of the duration of both events, 768 hours, lets say my user picks pivot slicers for the period of 1/1/2016 to 1/31/2016 to report on. The total duration of outage hours should then be calculated as 240 hours since the first 20 days of the first outage don't fall within the reporting period.

Hope this makes some sense. Thanks All!

 

[Ozeroth]

Hi smaur,

The measure you are wanting is a variation on 'events in progress', see this paper:
http://www.sqlbi.com/wp-content/uploads/DAX-Query-Plans.pdf

I've taken the DAX code on page 27 of that paper and modified for your situation - can you see if it does what you expect?

Assumptions:

• You have a Calendar table with column Calendar[Date]
• There is no relationship between ProductionStop and Calendar
• The columns ProductionStop[dateOffline] and ProductionStop[dateOnline] can contain arbitrary DateTime values, i.e. they don't have to be midnight. So you can have outages for fractional numbers of days.
• The selection on Calendar[Date] doesn't have to be contiguous for the measure to work.

I tried a few variations and this one seemed to perform best. Maybe someone else can come up with a better implementation

=
SUMX(
    VALUES ( Calendar[Date] ),
    SUMX (
        ADDCOLUMNS (
            FILTER (
                GENERATE (
                    SUMMARIZE (
                        ProductionStop,
                        ProductionStop[dateOffline],
                        ProductionStop[dateOnline],
                        "Rows", COUNTROWS ( ProductionStop )
                    ),
                    DATESBETWEEN ( Calendar[Date], ProductionStop[dateOffline], ProductionStop[dateOnline] )
                ),
                Calendar[Date] = EARLIER ( Calendar[Date] )
            ),
            "Hours",
            24 * (
                IF ( ProductionStop[dateOnline] <= Calendar[Date] + 1, ProductionStop[dateOnline], Calendar[Date] + 1 )
                - IF ( ProductionStop[dateOffline] >= Calendar[Date], ProductionStop[dateOffline], Calendar[Date] )
            )
        ),
        [Rows] * [Hours]
    )
)

"Rows" is the number of rows with the same dateOffline/dateOnline.
"Hours" is the number of hours the outage intersects with a given day.
These get multiplied together then summed.

 

[smaur]

Thanks for the link Ozeroth, and the suggested code, I'm going to take a look at this and try it out. I check back later and let you know how it works out.

 

[smaur]

Works perfectly, Thanks again Ozeroth!

 

[smaur]

So I looked at the link provided above by Ozeroth to try to understand how and why the DAX he provided works and I have to say I'm in over my head. If someone can provide a little insight into what is going on with this function or point me in the right direction to get started on figuring it out I'd really appreciate it

 

[Matt Allington]

This is very complex DAX. Don't beat yourself up if you don't understand it. No one could write this formula starting from the top and working down. They way to author these formulas is from the inside out. So you create a mental plan based on what you need to do, the start building from the inside out. My guess is that Ozeroth started with the Summarize line first and built a table to use inside sumx. If it were me, I would write this in a DAX studio so I could see what I was doing. Then wrapped it in the generate and addcolumns to build out the extra info needed in the table. Once the table is built, he wrapped it in a sumx and created the logic of the calculation that occurs at each step of the iteration. The if statements are used to get the correct logic (I haven't invested any time understanding the requirement so I am not clear on this logic). Finally he wrapped it in another sumx to execute the process for every day.

My point is not to explain the formula to you, but instead to explain the process of writing complex formulas like this. And even experienced authors may need to break the formula apart using the same process to understand it.

given you know the business logic, if you want to learn, then I suggest you read my blog about DAX studio and then follow the process steps I have explained above and repeat the process yourself. This will help you get your head around it. http://exceleratorbi.com.au/getting-started-dax-studio/

 

[smaur]

I've taken Matt's advice and am using DaxStudio to better understand the measure Ozeroth provided above by analyzing it step by step from the inside out.

I first ran the SUMMARIZE portion in DaxStudio, and then expanded to include GENERATE, and it provided the tables I expected.

I ran into trouble when I Evaluated the FILTER portion which makes use of the EARLIER function. In researching I see that it is difficult to get EARLIER to work in a Measure due to the lack of row context, it results in the error "EARLIER/EARLIEST refers to an earlier row context which doesn't exist".

The same error results with FILTER wrapped in ADDCOLUMNS.

My question is am I doing something wrong, Are there other things I can do to get more understanding of how the Measure works using DaxStudio. The potential is intriguing but I'd appreciate any other help.

In the meantime I'm reading this post by Javier: https://javierguillen.wordpress.com/2012/02/06/can-earlier-be-used-in-dax-measures/
to better understand what is going on.

Thanks in advance!
-Steve

 

[Matt Allington]

I think your issue is you have added the filter and not the sumx. The earlier fipunction only works when it is embedded inside the sumx (sumx contains the earlier row context). I suggest just add the filter and hard code a date instead of the earlier function. Then add the sumx and add back the earlier function knowing it refers to the earlier filter context in sumx. Another point to note is these formulas are expecting an initial filter context from your pivot table. If you do t have one (like in dax studio) it may make sense to simulate one by wrapping the entire formula in another filter function that is solely there to simulate a row in your pivot.