Hi Linda,

Please upload a sample file in Excel (just a small sample with enough data to illustrate the scenario). Please also give an example of the desired result (also in Excel) that refers back to the data so we can follow what you mean.

Mynda

Hi Mynda,

The desired outcome is to develop two line graphs and two corresponding tables in Power BI Desktop. The first graph/ table set will show the cumulative distinct count of event participants and the second set will show the cumulative paid amount. The tricky part is showing the cumulative patterns in 4 day intervals, from 60 days before the event date and stopping at the event date. The event date is a different date each year.

For example for the event code SUP2018, the first graph will show a distinct count of:

At the -60 day mark (18 April 2018), 2 participants who have registered (by counting the number of rows where EventRevenueType = Registration)

At the -56 day mark (22 April 2018), 3 participants who have registered

At the -52 day mark and so on until the event date.

While the second graph will show a cumulative paid amount of:

At the -60 day mark (18 April 2018), it will show $40

At the -56 day mark (22 April 2018), it will show $405

At the -52 day mark (26 April 2018) it will show $425

And so on until the event date.

Thank you Mynda

Hi Linda,

Thanks for sharing your example file. I think you might be best to generate the running total and day marks in Power Query. See file attached. I used the Grouped Running Total as explained here. And in the EventDates query I used Expand Rows technique covered in session 7.08 of the Power Query course. Hope that points you in the right direction.

Mynda