Forum Discussion
Helper Icumulative counts of data points between two dates
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.
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
5 Replies
- JarroVGIT
Resident Rockstar
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! 🙂
prakritnepalThank you for the suggestion, for some reason the countrows piece is not working. I am getting blanks on all of those.
- JarroVGITIs startdate and enddate of type DateTime in your original table?
v-lili6-msftCommunity Support
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
