Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
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:

customerstartdatestopdate
as101/01/201723/08/2020
fg315/11/201805/03/2021
ty714/05/202023/02/2022
ty706/10/201915/07/2022
az101/01/2012023/08/2022
df315/11/201805/03/2019
to714/05/201923/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.

grindle_0-1650982816932.png

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
philouduv
Resolver III
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.

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 🙂

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.