cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
pauste1 Frequent Visitor
Frequent Visitor

Running Total of event registrations by custom 7-day interval per event category

EDIT: Added example files here

Hi, I would like to display a running total of event registrations over a date range, sliced by event category, but with some variations to the typical 'running total over time' scenario.

 

The date range is the 7 day intervals, (not Mon-Sun week intervals) aka weeks prior to the event Week 0, Week 1, Week 2, etc. Any event registrations more than 26 weeks prior to the event are grouped into the Week 27+ interval.

 

Events can have different event dates, and multiple events exist in each event category (Dark Blue, Red, etc, in image)

This is what the table looks like in Excel with raw data that has been manually collated and manually entered, and I would like to replicate the table in Power BI with measures and fields.

EventRegistrationsByGroup.png

EventRegistrationDataModelRelationship.png

I have the Event Registration table that contains a record for every registration with the date of registration and the event.

[Registration Date],[Event],[EventKey]

30 June, 2019|Event 2|20190630Event2

30 June, 2019|Event 2|20190630Event2 -- could be multiple registrations for same event on same day

30 June, 2019|Event 1|20190630Event1

4 July, 2019|Event 1|20190704Event1

 

I have built an 'Event Calendar' table that contains a date record for every event for every date in the 12 months prior to the event date (every event will have a different 12 month period, but there will be some dates common to the 12 month period of events, but they could be in different Weeks.

For example: Event 1 on Aug 1, 2019 and Event 2 on July 1, 2019; when looking at date June 30, 2019, the date is Week 5 for Event 1 and Week 1 for Event 2. 

[Date],[Event],[EventKey],[Week Num],[Week Num Label]

June 30, 2019|Event 1|20190630Event1|5|5

June 30, 2019|Event 2|20190630Event2|1|1

 

I can successfully build the Running Total over a calendar date range, (as shown below), but I need the running total over the 7-day intervals.

EventRegistrationTableByCalendarDate.png

But calculating for the Week Num Label still just represents the total for that week, and not the running total of the preceding weeks (see below - Week Num Label Sort is an integer field containing the week as a num, and is used for sorting the text field 'Week Num Label' in correct order)

EventRegistrationTableByWeekNum.png

There is a Slicer on the page for the year of the events to filter all data to events occuring in a particular year, but the 12 month registration period for an event can extend into the previous calendar year.

 

Question: How do I get the matrix of Week Num Label and Event Category to have the running total?

 

Thanks for your help!

EDIT: Example.xlsx and Example PBIX files are located here.

Example-EventRegistrationMatrix.png

1 ACCEPTED SOLUTION

Accepted Solutions
pauste1 Frequent Visitor
Frequent Visitor

Re: Running Total of event registrations by custom 7-day interval per event category

I resolved this calculation by removing the relationships between Event Registration and Calendar Date, Event Calendar as apparently the issue was trying to calculate a running total between two linked tables.

When you have an active data relationship between two tables, Power BI doesn't like to do cumulative calcs -- it just returns basically the same row-by-row value as if you did a straight sum, or dropping in a column from a related table onto a viz that has categories from the table you want to summarize.
 
Doing this created the running total on the week labels.

 

Running Registration Total = 
var EventYear = Year(MAX('Event Registration'[Event Begin Date]))

RETURN 
    calculate(COUNT('Event Registration'[Registration Date]),
    filter(ALL('Event Registration'),
    'Event Registration'[Week Num Colmn] >= MIN('Event Calendar'[Week Num])),
    filter(ALL('Event Registration')
)

View solution in original post

4 REPLIES 4
Super User
Super User

Re: Running Total of event registrations by custom 7-day interval per event category

I'm afraid that without sharing some example data you won't get a definite answer.

Please anonymize a subset of the data and paste a link or attach a file. In the file you could state what the expected result is for the current state of affairs.

Thanks.

Best
Darek
pauste1 Frequent Visitor
Frequent Visitor

Re: Running Total of event registrations by custom 7-day interval per event category

Thanks, @darlove 

I edited the original post to link to example PBIX and XLSX files for download.

Super User
Super User

Re: Running Total of event registrations by custom 7-day interval per event category

OK. Thanks. I'll have a look as soon as possible.

Thanks.

Best
D.
pauste1 Frequent Visitor
Frequent Visitor

Re: Running Total of event registrations by custom 7-day interval per event category

I resolved this calculation by removing the relationships between Event Registration and Calendar Date, Event Calendar as apparently the issue was trying to calculate a running total between two linked tables.

When you have an active data relationship between two tables, Power BI doesn't like to do cumulative calcs -- it just returns basically the same row-by-row value as if you did a straight sum, or dropping in a column from a related table onto a viz that has categories from the table you want to summarize.
 
Doing this created the running total on the week labels.

 

Running Registration Total = 
var EventYear = Year(MAX('Event Registration'[Event Begin Date]))

RETURN 
    calculate(COUNT('Event Registration'[Registration Date]),
    filter(ALL('Event Registration'),
    'Event Registration'[Week Num Colmn] >= MIN('Event Calendar'[Week Num])),
    filter(ALL('Event Registration')
)

View solution in original post

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 64 members 1,122 guests
Please welcome our newest community members: