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

[pauste1]

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.

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.

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)

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.

[Anonymous]

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]

Thanks, @Anonymous 

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

[Anonymous]

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

Thanks.

Best
D.

[pauste1 · Accepted solution]

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')
)