0

We are investigating PowerBI for a reporting solution and it does a lot of what we need. However we need to be able to do adhoc reporting on events. Each event has a start date and an end date and total seconds and do percentage calculations etc. This works really well.

However our common requirement is to specify a start date and end date of which we wish to consider data. Many of the events will span over start and end of required period or even start before the start of period and go beyond the end. What we need to do is only consider the part of events that falls within the period.

Is it possible to use a slicer to define a start/end period and then only include the number of seconds within the period sliced for each event?

This would allow us to calculate the total time for all events that fell within the period.

Update

My table consists of hundreds of thousands of rows like

EventID|VehicleID|StatusID|ReasonCodeID|StartDateTime|EndDateTime|TotalDuration

We need to look at portions of each event that fall within a selected period (start/end). However events can span periods. If part of an event is outwith a period we want to ignore that part of the duration. If whole event is outside selected period then we would ignore all of it.

For example say an event starts on 1st Feb and goes to 1st July. If the selected date range of slicer was 1st Jan to 1st March then I want to only include the time between 1st Feb and 1st Mar in Total Duration calculation

1
  • Also does anyone know is there some sort of equivalent of SQL Fiddle for DAX/PowerBI so I can demonstrate what I am talking about? Commented Nov 4, 2016 at 0:57

2 Answers 2

1
+100

What you're asking for is doable, but how well it works would very much depend on the amount of data in your table.

You can use a DAX measure to calculate the sum of the duration for any events that fall on a given date. You can do this using a disconnected date table that you slice on (the October 2016 Power BI Desktop release includes a built-in data slicer that allows you to easily pick a range). More information on that here: https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-october-feature-summary/#reportView.

You would then have a CALCULATE measure that SUMS the duration, with FILTER to ensure the event in question lands within the date range selected by the slicer. There's more specifics on this part of the question here: Optimizing Dax & model for "where date between" type queries

However, you're going one step further, in that you don't want to SUM the full duration of events that fall within a given date range - you only want to sum the duration that falls within the given date range.

In order to do that, you have to calculate the duration for each individual row at run time based on the selected date range. You can do this with a measure that uses SUMX (see below), but over a large number of records (thousands, millions) the calculation will start slowing down.

For example, if you have a disconnected date table called Date, and your event table is called Event, you can have a measure such as:

Filtered Duration =
CALCULATE (
    SUMX (
        Event,
        DATEDIFF (
            MAX ( MIN ( 'Date'[Date] ), Event[StartDateTime] ),
            MIN ( MAX ( 'Date'[Date] ), Event[EndDateTime] ),
            SECOND
        )
    ),
    FILTER (
        'Event',
        'Event'[StartDateTime] <= MAX ( 'Date'[Date] )
            && 'Event'[EndDateTime] >= MIN ( 'Date'[Date] )
    )
)

MIN('Date'[Date]) in this case formula corresponds to the earliest date in the disconnected date table that is within the selected date range. MAX('Date'[Date]) corresponds to the latest date.

The last part (the FILTER) is saying "only look at events that land on a date within the range selected". The SUMX is saying "for each row, do a DATEDIFF". The MAX within the DATEDIFF is saying "choose the later of either the first date from the slicer, or the start date of the event". The reason for this is that if you select the 15th through the 20th on your date slicer, and an event starts on the 18th, you want to count from the 18th. But if the event started on the 11th, you'd want to count from the 15th. The MIN is doing the opposite with the end date.

If an event falls within the time range entirely, then it will calculate the seconds from start to end. If the end of the event is after the selected time range (for example), then it will count the seconds from the exact start of the event to midnight of the selected end date.

Note that because I've used a date table you wouldn't be able to pick partial days as a date range. You could extend this to include a time table but it gets more complicated (you'd need a separate start & end time table and then account for that logic in the above, already quite complex, formula)

Sign up to request clarification or add additional context in comments.

5 Comments

Looks great - really appreciate this - this is probably the most complex thing we need to do with PowerBI. We really only need to pick whole days as start points so Date table would be fine. Will test tomorrow.
Works like a charm! I will add a bounty to this question and award it to you once it is open for a bounty. Thanks!
I'm glad you were able to get it to work. Good luck with the rest of your Power BI solution. Thank you!
I can award the bounty at some point tomorrow. Also I asked another question related to this - which also seems pretty complex - even any pointers on that would be appreciated.
Thanks for the bounty. It will make a big difference to me as I start out here. It looks like someone beat me to the other question but definitely let me know if it doesn't work. I will say I recommend using SQL and importing the data into Power BI as a single table, rather than importing the tables as-is and having a more complex measure to join them together on the fly. (Assuming there aren't other reasons you need the two tables separated.) Link to other question: stackoverflow.com/questions/40452027/…
1

Is it possible to use a slicer to define a start/end period and then only include the number of seconds within the period sliced for each event?

Yes, It is possible. In the Power BI Gallery there is a custom visualization that lets you filter date ranges.

You just have to drag and drop the date column in your model to the Timeline slicer in order to filter your measures.

To install it go to Gallery and search timeline in the search box, download it and import it to Power BI.

If you get stuck with the DAX expressions, include SQL code and a sample model to your question.

Let me know if this helps.

2 Comments

Appreciate that. I had seen that slicer and installed it and looks good. My issue is that it simply uses a single field in my data to filter what it rows are shown. I think I need to use some sort of DAX expression to recalculate my TotalSeconds column depending on how much event is within selected date period. Will update question
@GraemeMiller, How is TotalDuration column being calculated? It represents seconds from StartDateTime to EndDateTime?. What happens when the event range in greater than the slicer range?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.