Forum Discussion
Count Number of Rows if Date is Between Two Columns
Hello,
Been scratching my head for days on this one, any help much appreciated!
I have a Calendar Table that lists every date in 2023.
I have another table (Table 1) that has three columns; Start Date, End Date and a Customer ID.
I am trying to build a Dax formula in my Calendar Table that does the following; for each day of 2023 it counts the number of unique customer IDs where the start date is on or before and the end date is on or after (e.g. the date sits in between).
Thanks,
Sam
8 Replies
- Greg_Deckler
Community Champion
SJCee Try:
Measure = VAR __Date = MAX('Calendar'[Date]) VAR __Table = FILTER('Table', [Start Date] >= __Date && [End Date] <= __Date) VAR __Result = COUNTROWS(DISTINCT(SELECTCOLUMNS(__Table, [Customer ID]))) RETURN __ResultThanks Greg_Deckler , I have applied your forumla, but it only counts rows against the [Calendar]"Date" dates based on the [Table]"Start Date". I need to count for every single day in 2023 for example
Table:
Customer ID - 1, Start Date - 01/05/2023, End Date - 05/05/2023
Customer ID - 2, Start Date - 03/05/2023, End Date - 08/05/2023
Calendar Table:
01/05/2023 - Measure Result = 1
02/05/2023 - Measure Result = 1
03/05/2023 - Measure Result = 2
04/05/2023 - Measure Result = 2
05/05/2023 - Measure Result = 2
06/05/2023 - Measure Result = 1
07/05/2023 - Measure Result = 1
08/05/2023 - Measure Result = 1
As you can see 04/05 & 05/05 measure returns 2 as the dates fall inbetween start and end for both customers.
Cheers,
Sam
- Greg_Deckler
SJCee Do you have a relationship between your tables? If so, remove it. Or, switch to this:
Measure = VAR __Date = MAX('Calendar'[Date]) VAR __Table = FILTER(ALL('Table'), [Start Date] >= __Date && [End Date] <= __Date) VAR __Result = COUNTROWS(DISTINCT(SELECTCOLUMNS(__Table, [Customer ID]))) RETURN __Result
