counting rows based on multiple dates

[Anonymous]

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.

[philouduv]

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]

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

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,

[amitchandak · Accepted solution]

@Anonymous , refer if one of the two blogs can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-or-end-date/ba-p/1503785

[Anonymous]

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 🙂