Forum Discussion
Count on Graph for values WithIn Dates
Hi ,
Was wondering of you could help... i am very new to BI (as you will see) but thought i would ask.
I am trying to reflect the Count of the PersonID per month between Start and End Date.
The key being that it only counts the PeDistinct Person Code of the date shown on the Graph is within the Start and End Dates.
So if a Person Code is Between March and September 2017 - it will be counted once for each month.
TIA
| Person Code | Start Date | End Date |
| 1 | 03/12/2018 | 15/02/2019 |
| 2 | 03/12/2018 | 15/02/2019 |
| 3 | 03/12/2018 | 15/02/2019 |
| 4 | 03/12/2018 | 15/02/2019 |
| 1 | 07/08/2017 | 01/12/2017 |
| 2 | 07/08/2017 | 01/12/2017 |
| 3 | 07/08/2017 | 01/12/2017 |
| 4 | 07/08/2017 | 23/03/2018 |
| 1 | 15/05/2017 | 15/11/2018 |
| 2 | 15/05/2017 | 19/06/2017 |
| 3 | 15/05/2017 | 15/11/2018 |
| 4 | 09/05/2017 | 10/05/2017 |
HI Anonymous,
You can't direct use these date column to achieve your requirement, please take a look at following link to know how to create a detail data table to store expand date range and use it to direct calculate with records in date range.
Reference link:
Spread revenue across period based on start and end date, slice and dase this using different dates
Sample table formula:
Detail person records = VAR _calendar = CALENDAR ( MIN ( Table[Start Date] ), MAX ( Table[End Date] ) ) RETURN SELECTCOLUMNS ( FILTER ( CROSSJOIN ( Table, _calendar ), Table[Start Date] <= [Date] && Table[End Date] >= [Date] ), "Person Code", [Person Code], "Date", [Date] )Notice: please don't forget to create relationship between new table and original table based on 'person code'.
Regards,
Xiaoxin Sheng
2 Replies
HI Anonymous,
You can't direct use these date column to achieve your requirement, please take a look at following link to know how to create a detail data table to store expand date range and use it to direct calculate with records in date range.
Reference link:
Spread revenue across period based on start and end date, slice and dase this using different dates
Sample table formula:
Detail person records = VAR _calendar = CALENDAR ( MIN ( Table[Start Date] ), MAX ( Table[End Date] ) ) RETURN SELECTCOLUMNS ( FILTER ( CROSSJOIN ( Table, _calendar ), Table[Start Date] <= [Date] && Table[End Date] >= [Date] ), "Person Code", [Person Code], "Date", [Date] )Notice: please don't forget to create relationship between new table and original table based on 'person code'.
Regards,
Xiaoxin Sheng