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

    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'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.