cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

## 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 - with startdate and stopdate columns:

 customer startdate stopdate as1 01/01/2017 23/08/2020 fg3 15/11/2018 05/03/2021 ty7 14/05/2020 23/02/2022 ty7 06/10/2019 15/07/2022 az1 01/01/20120 23/08/2022 df3 15/11/2018 05/03/2019 to7 14/05/2019 23/02/2021

My objective is to count the amount of active customers on the 1st day of each month and then chart it to create a result like the below (and to have the result filterable - as my real data has extra columns and a slicer). My definition for active is: startdate <= mth_start_date < stopdate.

I have built a date table called "first_of_mth" that I have created a relationship to customer[startdate]. first_of_mth has data like this (but running up to the current date):

 mth_start_date 01/01/2017 01/02/2017 01/03/2017 01/04/2017 01/05/2017 01/06/2017 01/07/2017 01/08/2017 01/09/2017

The closest I have got to achieving what I want with DAX is:

Active =

CALCULATE (

COUNTROWS ( customer ),

FILTER (

customer ,

max(first_of_mth[mth_start_date]) >= customer[startdate]

&&  max(first_of_mth[mth_start_date]) <= customer[stopdate]

)

)

But what this does is count the number of rows where startdate = mth_start_date.

I get the impression I need to do something with both relationships between tables and DAX to get this working. Thank you.

1 ACCEPTED SOLUTION
5 REPLIES 5
Resolver III

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
Not applicable

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

Resolver III

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,

Anonymous
Not applicable

Thanks for this amitchandak - your first blog post did what I was wanting. Very helpful! It showed me that I needed 2 inactive relationships and then my DAX worked 🙂

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors