cumulative counts of data points between two dates

[prakritnepal]

I am having trouble figuring out how to calculate and display the number of people that were on vacation on any given day. 

I have a SharePoint list with all the data. The data I am trying to use is

1. start date

2. end date

3. person name

There are other columns as well that goes with regular vacation request like type, approved, etc.

Let's take the following example:

start date	end date	person
12/15/2019	12/20/2019	A
12/17/2019	12/22/2019	B
12/19/2019	12/24/2019	C

If we plot a bar graph, the number of people on vacation on 12/19/19 would be 3, all A B and C

12/17/19 would be 2,  B and A

12/16/19 would be 1, A

I was thinking of creating a calculated table but I have not been able to come up with a proper logic that works. Any help is appreciated.

[v-lili6-msft · Accepted solution]

hi @prakritnepal 

Just use this logic to create a table

New table = FILTER(GENERATE('Table',CALENDARAUTO()),[Date]>=[start date]&&[Date]<=[end date])

and here is sample pbix file, please try it.

Regards,

Lin

[prakritnepal]

Thank you. That worked

[JarroVGIT]

A calculated table will definitely help! It's a variation of a typical datetable:

Table = ADDCOLUMNS(CALENDAR(MIN(Table1[startDate]), MAX(Table1[endDate])), "VacationPeople", COUNTROWS(FILTER(ALL(Table1), Table1[startDate] >= [Date] && Table1[endDate] <= [Date])))

 Table1 is a reference to your original table.

Let me know if this works for you! 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[prakritnepal]

Thank you for the suggestion, for some reason the countrows piece is not working. I am getting blanks on all of those. 

[JarroVGIT]

Is startdate and enddate of type DateTime in your original table?