Cumulative count of bookings specific to each event/subset

[jonnyko]

Hi Community!

Trying to get my head around the correct DAX to essentially look at event takeup as the date of the event nears. The visual should be filterable by region of the event e.g. takeup of all the events hosted in London, date of the event, and by data points of the individuals making the booking e.g. their city of residence.

I have:

Fact_Bookings - master list of all bookings made by individuals to all events ever hosted. Includes date of signup, date of event, calculation of 'signup days in advance', bins of signup advance days (the variable I want on my X axis)  

Dim_Events - master list of all events ever hosted, and their maximum capacity. The unique ID for this is a concatenation of Location (non-unique) and Date (unique)

I've tried DAX on a calculated column to show, for each booking, what the event takeup is after that booking i.e. (cumulative bookings for that event / maximum capacity of event), attempted along the lines of

Cumulative bookings =

Calculate(

         Count(Fact_Bookings[user ID]),

         relatedtable(Dim_Events),

         Fact_Bookings[signup date] <= earlier(Fact_Bookings[signup date]))

but there's clearly things wrong with this, even as a count rather than as a % of the relevant event's max capacity.

Any help would be much appreciated, thanks!!

[amitchandak]

@jonnyko , I think you can create a new column like in Fact_Bookings

countx(filter(Fact_Bookings, Fact_Bookings[signup date] <= earlier(Fact_Bookings[signup date])),Fact_Bookings[user ID])

if not

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[jonnyko]

It's not letting me post with a table because it says invalid HTML (no idea why). I've attached a screenshot of the data tables I'm working with and the desired column/calculation at the end instead hope that clarifies things for you?