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.