Count on Graph for values WithIn Dates

[Anonymous]

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

[Anonymous · Accepted solution]

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

[lpiz]

Hi there,

I was browsing on the internet and this is exactly what I needed, thank you. Now I would like to modify this for records that have an empty 'End Date', which I want to interpret as still active and therefore I would like to include them in a count. How would you do this?