Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

counting rows based on multiple dates

I've spent a lot of time trying to solve this and read many similar sounding questions but have not managed to solve this - although it seems straightforward.   I have "customer" data like this - w...
Need Help
philouduv's avatar
philouduv
Resolver III
4 years ago

Hey Anonymous ,

Go on your table first_of_mth and count each month nbr active customer from here:

nbr_custo = CALCULATE(COUNT(datasource[customer]),
FILTER(all(datasource),
datasource[startdate].[Date] <= datasource[mth_start_date].[Date]
&&
datasource[stopdate].[Date] > datasource[mth_start_date].[Date]))


Best regards,

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    philouduv - thanks for your reply. I've tried 2 variations of your suggestion. Both generated error messages - see below. Can you spot what's wrong?

     

    nbr_custo = CALCULATE(COUNT(customer[customer]),

    FILTER(all(prod),

    customer[startdate] <= first_of_mth[mth_start_date]

    &&

    customer[stopdate] > first_of_mth[mth_start_date]))

    Error message: A single value for column 'mth_start_date' in table 'first_of_mth' cannot be determined…

     

    nbr_custo = CALCULATE(COUNT(customer[customer]),

    FILTER(all(customer),

    customer[startdate].[Date] <= first_of_mth[mth_start_date].[Date]

    &&

    customer[stopdate].[Date] > first_of_mth[mth_start_date].[Date]))

    Error message: Column reference to 'startdate' in table 'customer' cannot be used with a variation 'Date' because it does not have any.

    • philouduv's avatar
      philouduv
      Resolver III
      4 years ago

      Hey Anonymous,

      First of all please ensure that your table with date have the correct format ( the second error point this on the table customer)

      Take again your first formula but be sure to create a column in the table "first_of_mth"

      nbr_custo = CALCULATE(COUNT(customer[customer]),

      FILTER(all(customer),

      customer[startdate].[Date] <= first_of_mth[mth_start_date].[Date]

      &&

      customer[stopdate].[Date] > first_of_mth[mth_start_date].[Date]))


      The .[Date] is only avalable if you have the good format (Date)

      Best regards,