Forum Discussion

SJCee's avatar
SJCee
Frequent Visitor
3 years ago

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 column...
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
3 years ago

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's avatar
SJCee
Frequent Visitor
3 years ago

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.