DAX SUM ACTIVE Records Between Start Date and End Date with Selected Date on Line Graph

[derekmac]

EmployeeID	Start Date	End Date
1	        01/01/2021	31/12/2021
2	        01/01/2022	31/03/2023
3	        01/08/2023	
4	        01/01/2023	
5	        01/03/2023	31/08/2023

For the above scenario I want plot the Total Record Count on a Line Graph that shows me the ongoing Active Records taking into account the Date on the X-Axis and taking into account the Start and End Date of the Employee.
Any help or pointers appreciated!

[tamerj1 · Accepted solution]

Hi @derekmac 
You need to have a disconnected date table. Your count measure would be:

Count =
SUMX (
    VALUES ( 'Date'[Date] ),
    COUNTROWS (
        FILTER (
            'Table',
            'Table'[Start Date] <= 'Date'[Date]
                && COALESCE ( 'Table'[End Date], TODAY () ) >= 'Date'[Date]
        )
    )
)

[tamerj1 · Accepted solution]

@derekmac 
Create a date table and keep it disconnected from the main table (no relationships). It should contain all the dates from minimum start date to the maximum end date. Example

Date = CALENDAR ( MIN ( 'Table'[Start Date] ), MAX ( 'Table'[End Date] ) )

Place 'Date'[Date] in a table visual along with the count measure

Count =
SUMX (
    VALUES ( 'Date'[Date] ),
    COUNTROWS (
        FILTER (
            'Table',
            'Table'[Start Date] <= 'Date'[Date]
                && COALESCE ( 'Table'[End Date], TODAY () ) >= 'Date'[Date]
        )
    )
)

[derekmac]

Hi

Total Prospects = 
SUMX (
    VALUES ( 'Dates'[End of Week] ),
    COUNTROWS (
        FILTER (
            'Prospects',
            'Prospects'[Date Added] <= 'Dates'[End of Week]
                && COALESCE ( Prospects[Date Closed], TODAY () ) >= 'Dates'[End of Week]
       )
    )
)

How would I amend the above to calculate only for the last 7 days? 

[tamerj1]

Hi @derekmac 

please clarify with some screenshots 

[derekmac]

I don't understand that

[tamerj1 · Accepted solution]

@derekmac 
Create a date table and keep it disconnected from the main table (no relationships). It should contain all the dates from minimum start date to the maximum end date. Example

Date = CALENDAR ( MIN ( 'Table'[Start Date] ), MAX ( 'Table'[End Date] ) )

Place 'Date'[Date] in a table visual along with the count measure

Count =
SUMX (
    VALUES ( 'Date'[Date] ),
    COUNTROWS (
        FILTER (
            'Table',
            'Table'[Start Date] <= 'Date'[Date]
                && COALESCE ( 'Table'[End Date], TODAY () ) >= 'Date'[Date]
        )
    )
)

[derekmac]

Target

1

2

3

4

5

If I had a 4th Column called Target, how would I change the DAX to sum this column using the disconnected table instead of Counting Rows?

I want to add a target line to my Line Graph too

Thank you so much for you help so far, I have not come across this technique before, it is quite advanced for me!

[tamerj1 · Accepted solution]

@derekmac 

Please try

Count =
SUMX (
    VALUES ( 'Date'[Date] ),
    SUMX (
        FILTER (
            'Table',
            'Table'[Start Date] <= 'Date'[Date]
                && COALESCE ( 'Table'[End Date], TODAY () ) >= 'Date'[Date]
        ),
        'Table'[Target]
    )
)

[derekmac]

Thank you so much

[tamerj1 · Accepted solution]

Hi @derekmac 
You need to have a disconnected date table. Your count measure would be:

Count =
SUMX (
    VALUES ( 'Date'[Date] ),
    COUNTROWS (
        FILTER (
            'Table',
            'Table'[Start Date] <= 'Date'[Date]
                && COALESCE ( 'Table'[End Date], TODAY () ) >= 'Date'[Date]
        )
    )
)