topic Re: DAX SUM ACTIVE Records Between Start Date and End Date with Selected Date on Line Graph in DAX Commands and Tips

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

derekmac — Tue, 20 Jun 2023 12:41:27 GMT

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!

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

tamerj1 — Tue, 20 Jun 2023 12:58:14 GMT

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] ) ) )

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

derekmac — Tue, 20 Jun 2023 13:15:15 GMT

I don't understand that

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

tamerj1 — Tue, 20 Jun 2023 14:01:37 GMT

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] ) ) )

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

derekmac — Wed, 21 Jun 2023 07:57:37 GMT

Target

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!

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

tamerj1 — Wed, 21 Jun 2023 08:04:18 GMT

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] ) )

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

derekmac — Wed, 21 Jun 2023 09:38:31 GMT

Thank you so much

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

derekmac — Tue, 04 Jul 2023 14:10:33 GMT

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?

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

tamerj1 — Tue, 04 Jul 2023 17:14:32 GMT

Hi derekmac

please clarify with some screenshots