Count Number of Rows if Date is Between Two Columns

[SJCee]

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

[Greg_Deckler]

@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
  __Result

[SJCee]

Thanks @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

[SJCee]

Thnaks @Greg_Deckler worked a treat. I am using the above measure in a bar chart (X-axis = 'Calendar Date'[Date], Y-axis = Measure), I then have a table below with the Customer ID details on the same page. I would like to click on one of the bars in the chart and for it to show the Customer IDs in the table, how would I go about this? I know relpationship mapping wont work as there is Start and End date in my data table and the single Date in the calendar table.

[Greg_Deckler]

@SJCee So for that, you could use a Complex Selector. The Complex Selector - Microsoft Power BI Community. In your case it would be a very similar measure perhpas like this:

Complex Selector =
  VAR __ID = MAX('Table'[Customer ID]) 
  VAR __Date = MAX('Calendar'[Date]) 
  VAR __Table = 
    SELECTCOLUMNS(
      FILTER(ALL('Table'), [Start Date] >= __Date && [End Date] <= __Date),
      "ID", [Customer ID]
    )
  VAR __Result = IF( __ID IN __Table, 1, 0 )
RETURN
  __Result

[SJCee]

Thanks @Greg_Deckler , applied the above formula but now not returning any results. Let me know if it is easier if I start a new thread with this question.

[Greg_Deckler]

@SJCee Might be better that way so that the original question can be marked as answered. So, the idea behind this is that you would use this Complex Selector in a second visual with your Customer ID column in it. This measure would be in the Filters pane and set to filter for 1. In theory, when you clicked a Date in your original visual the second visual would get filtered to the ID's where that measure returns 1.

[SJCee]

Thanks @Greg_Deckler makes sense on the ComplexSelector. Before I create a new thread, I have notied quite a few of the numbers on the measure are actually incorrect on the Measure.

I have noticed on the measure forumla [CustomerId] on line 4 ("VAR __Result") is underlined in red and when I hover over it, it says "Parameter is not the correct type. Cannot find name "[CustomerId]"). CustomerId definitely matches the exact column name, so not sure what the issue is.