Dynamic calculation based on slicer value

[nixonyx]

I am trying to build a report that shows the impact of changing the start time of an event on the set up times and attendee arrival profile. This is then compared against other events occurring at the same time to help even out peaks in attendee arrival.

Table one (Event) has information on when an event is scheduled - it contains:

• the planned start time - named PStart
• the expected set up time (PStart - x minutes) - named ESetUp
• the expected team arrival time (ESetUp - y minutes) - named ETeam
• the expected distribution of people arriving in 5 minute buckets (planned start + 11 buckets of time) - named EArrive

Table two (Adjust) is a simple table from -60 to +60 in increments of 10 - named TAdjust

The report comprises a simple table summarising the the three time stamps (and some blurb about the event) and a chart that shows the distribution of people arriving, and the distribution of people arriving for other events occurring around the same time (I haven't figured that bit out yet, but that's the aim and is likely to be another question I'll be asking).

What I'd like to have happen:

The user selects an event from the list.

They then select a value from TAdjust that represents how much they want to change the selected event start time by.

PStart, ESetUp and ETeam are recalculated and displayed in a card or simple table alongside the original data.

The 11 EArrive values needs to be recalculated by the value of TAdjust and these revised values should be displayed on a new chart that shows the presentation profile of events occurring around the same time as the newly adjusted PStart.

I hope this all makes sense, I will try and anonymise the data and provide a sample if you need something to work with. Do ask if something needs clarification!

Thanks for your help!

[nixonyx]

Hi @Icey 

I have one more request, if I may.

I'm struggling to get the chart to work as expected. It seems to require me to add filters (date and locationID) to the filter panel or else it just shows the attendee presentation profile for all events on all dates.

I need the chart to show the attendee profile for all events within 30 minutes of the start time on the date selected and at the same location the event is taking place at.

Thank you very much for your help.

[Icey · Accepted solution]

Hi @nixonyx ,

I have adjusted your PBIX. See the attached file.

For your WISH LIST,

1. only show events on the 'Expected' chart starting within 30 mins of selected event;

• Create a EventID Table without any relationship among other tables.

EventID Table = VALUES('sample'[EventID])

• Create a measure.

Adjust ExpAttendees = 
IF (
    (
        MAX ( 'sample'[EventStart] ) >= ( [CurrentEventStart+AjustTime] - 30 / ( 24 * 60 ) )
    )
        && (
            MAX ( 'sample'[EventStart] ) <= ( [CurrentEventStart+AjustTime] + 30 / ( 24 * 60 ) )
        ),
    MAX ( 'sample'[ExpAttendees] )
)

2. change the ExpSetUpTime, ExpReadyTime, EventStart by 'Adjust event start time' selected value and display the new values (e.g. in a card);

• Create Measures.

Selected EventID = SELECTEDVALUE('EventID Table'[EventID])

Selected EventDate =
CALCULATE (
    MAX ( 'sample'[EventDate] ),
    FILTER ( ALL ( 'sample' ), 'sample'[EventID] = [Selected EventID] )
)

SelectedThemeID =
CALCULATE (
    MAX ( 'sample'[EventThemeID] ),
    FILTER ( ALL ( 'sample' ), 'sample'[EventID] = [Selected EventID] )
)

Selected EventSubThemeID =
CALCULATE (
    MAX ( 'sample'[EventSubThemeID] ),
    FILTER ( ALL ( 'sample' ), 'sample'[EventID] = [Selected EventID] )
)

SelectedLocalID =
CALCULATE (
    MAX ( 'sample'[LocationID] ),
    FILTER ( ALL ( 'sample' ), 'sample'[EventID] = [Selected EventID] )
)

Selected ExpSetUpTime =
CALCULATE (
    MAX ( 'sample'[ExpSetUpTime] ),
    FILTER ( ALL ( 'sample' ), 'sample'[EventID] = [Selected EventID] )
)

Selected ExpReadyTime =
CALCULATE (
    MAX ( 'sample'[ExpReadyTime] ),
    FILTER ( ALL ( 'sample' ), 'sample'[EventID] = [Selected EventID] )
)

Selected EventStart =
CALCULATE (
    MAX ( 'sample'[EventStart] ),
    FILTER ( ALL ( 'sample' ), 'sample'[EventID] = [Selected EventID] )
)

3. only show events on the 'Adjusted' chart starting within 30 mins of adjusted event start time for the selected event.

What's the difference between 1 and 3? Sorry, I can't understand it well.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nixonyx]

@Icey 

I've had a look at the excellent work you've done and I can see how to make 1 and 3 work for me, using your examples.

This is exactly what I was hoping to achieve, so I've accepted your solution and am very, very grateful for your help.

Happy New Year!

[nixonyx]

Hi Icey

Thank you so much! I will take a closer look today.

The difference between 1 and 3 is the time stamp used. 1 uses the original event start and 3 uses the adjusted event start.

For example, if an event was due to start at 10:00, the Expected chart would show the attendee presentation profiles for all events starting between 09:30 and 10:30.

If the event start was then adjusted to 10:15, the Predicted chart would show the attendee presentation profiles for all events (including an adjusted presentation profile for the one updated) starting between 09:45 and 10:45.

The two charts would then be compared to see if a peak in arrival was evened out or made worse.

Hope that makes sense!

[Icey]

Hi @nixonyx ,

Please share me a dummy PBIX file or sample data, without real data or sensitive information.

Best Regards,

Icey

[nixonyx]

Hi @Icey 

Thank you for coming back to me, here's a link to two files - a pbix and csv.

Link to sample files