Calculating the proportion of multiple events between two reporting dates

[gcarvel]

Hello folks,

I have a query with regards to calculating the proportion of multiple events, all of varying length and with different start and end times, within a set reporting period.  I have been looking around on this board and generally online, and as yet have still to find a solution that does exactly what I need it to.  Apologies if I have missed this.

I have created a sketch to try to help me explain my objective:-

Each bar indicates an event; in this case the temporary closure of a property.  I wish to calculate the number of days which fall within my start and end reporting dates (dotted lines).  I wish to account for the proportions in red – this includes closures which started before the first date of reporting, but I only want to account for the days of that closure which occurred from the start of the reporting period onwards.  Similarly, with closures that start within the report and overrun – I only want the days up to the final reporting day.  I would like to run reports for any time period i.e. financial year, monthly, or any other custom period for ad hoc reports.  The number of closure ‘events’ could run into the hundreds as there are many sites included in the dataset.

As a further guide to what I am trying to achieve, I found the following DAX formula online which I have applied to my data:-

Filtered Duration =

CALCULATE (

    SUMX (

        ClosuresMaster,

        DATEDIFF (

            MAX ( MIN ( 'Calendar'[Date] ), 'ClosuresMaster'[Date of Closure] ),

            MIN ( MAX ( 'Calendar (2)'[Date] ), 'ClosuresMaster'[Date of Re-opening] ),

            DAY

        )

    ),

    FILTER (

        'ClosuresMaster',

        'ClosuresMaster'[Date of Closure] <= MAX ( 'Calendar'[Date] )

            && 'ClosuresMaster'[Date of Re-opening] >= MIN ( 'Calendar (2)'[Date].[Date] )

    )

)

This formula almost gives me what I need but it still fails to pick up those closures that started before the first reporting date i.e. the bar at the top left of the diagram, or the second bottom.  You will note that I created two calendars (one for Date of Closure, and one for Date of Re-opening) as I turned off the auto date/time function.  I’d be interested to know if this is the right thing to do or if there is a better approach?

Thank you in advance for any assistance!

[Anonymous]

This is very easy to do but you have to think in a different way. You should create a (hidden) table that will store columns EventID and Date for all dates on which the events lasted, which means it'll store all combinations EventID and Date between the event start date and the event end date. Then this table should be connected to the dimensions: Events and Dates (one-way filtering from dims to this bridge table, let's call it EventToDateMap). Now, if you select events from Events and a period of time from the Dates table (say, a month), you'll easily get the number of days that intersect the selected interval. You'll just do: distinctcount( EventToDateMap[Date] ). And you're done.

By the way, it's best not to have to deal with blanks. If you have a blank end date, just put something like 3000-01-01 in there and do not connect this to any dimension. You won't have to connect if you do what I said above. Of course, the brigde table should only store data up to today, so if you have an event that has not yet finished, then just store the combinations of EventId, Date up until today. Tomorrow you'll refresh the model and everything will be updated just right. Anyway, for the analysis you want, you should not set the boundary of the reporting period into the future, so this should not be a problem. Please remember that dealing with blanks is rarely a good idea. They will make your code more complex and slower than it could be.

[Greg_Deckler]

Seems like you might want Open Tickets. https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364#M147

[gcarvel]

Hi @Greg_Deckler ,

Sorry for taking so long to come back to you about this - I've only just got round to investigating this again and testing a few things.  Apologies also if this comes through twice as I thought I had sent a moment ago but seems to have been an error.

Thanks for suggesting the Open Tickets solution.  Having tried this, it unfortunately doesn't seem to be what I am looking for, but I think I've sussed my issue and would be grateful for your advice on how to write this into DAX.

The DAX I inserted with my original post does seem to be what I need, but my issue was that it was not picking up closure events which do not yet have a 'date of re-opening' (end date).  I thought it was missing some of the events which began before the first date of the period slicer, but when I tested a dummy end date, they would appear in my visual.  These are longer-term closures.

What I basically want to ask of DAX, is whenever it finds a blank cell under the 'Date of Re-opening' column in my main dataset - I would like to automatically consider this to be the end date of the period report - the second date on the date slicer.  I would hope this is fairly straightforward to do but I'm just not sure of the correct syntax for this within the above DAX formula?  Any pointers with this would be great.

Many thanks,

Gary

[gcarvel]

Hi again @Greg_Deckler 

I just wanted to say that I've been experimentling with this and have now found a solution to my problem:-

Filtered Duration =

CALCULATE (

SUMX (

ClosuresMaster,

DATEDIFF (

MAX ( MIN ( 'Calendar'[Date] ), 'ClosuresMaster'[Date of Closure] ),

MIN ( MAX ( 'Calendar'[Date] ), ( IF (

ISBLANK (

'ClosuresMaster'[Date of Re-opening] ),

[Latest Date in slicer], ClosuresMaster[Date of Re-opening] ) ) ),

DAY

)

),

FILTER (

'ClosuresMaster',

'ClosuresMaster'[Date of Closure] <= MAX ( 'Calendar'[Date] )

&& ( IF (

ISBLANK (

'ClosuresMaster'[Date of Re-opening] ),

[Latest Date in slicer], ClosuresMaster[Date of Re-opening] ) )

>= MIN ( 'Calendar'[Date] )

))

I created a measure for the latest date in the slicer:-

Latest Date in slicer =

CALCULATE ( LASTDATE ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar' ) )

I then used this in the IF function to provide the second date where the 'Date of Re-opening' is blank in my base datasheet.

The above DAX formula gives me exactly what I need.  So I can now chalk this off as solved, to save yourself or anyone else more time in looking at this.

Thanks again,

Gary

[gcarvel]

Many thanks for your response, Greg.

I'm working about with it just now and maybe have a little work to do to fit in with my dataset but I'll let you know how I get on.