Counting People per Day From a List With Start & End Dates

[Bruce Montemayor]

Hello All, this has been gnawing at me and I just can't get it to work.

I have one table with the following fields:
-Name
-Event
-Specialty
-Start Date
-End Date

I would like to have a table of dates, let's say one year, and I would like to have a column counting the number of people per day.

So for January 1, how many people are there where January 1 falls between their start and end date?

I would like to be able to slice or aggregate by event and/or specialty.

Essentially, I want to be able to show a grand total for each day, and as another view, number of people per specialty per day. Then another pivot, the number of people per specialty per event.

I do understand that once the measure is built, I can simply place the other fields when and where needed. But the measure is my Bigfoot/Loch Ness Monster/(name your elusive creature).

Any help will be greatly appreciated. Thanks Much!

 

[Matt Allington]

You have 2 date columns in the data table, so I suggest not using any relationships. Besides if you create relationships you will likely have problems when time period analysis cross year end. Instead you can apply virtual relationship using filter functions. Something like this (not tested)

calculate(countrows(dataTable),
   Filter(dataTable,dataTable[from date] <=min(calendar[date]) && dataTable[to date] >= max(calendar[date])))

i guess this will only count those that are there at the start and the end.