Forum Discussion
counting rows based on multiple dates
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.
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,