Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Solved! Go to Solution.
@Anonymous , refer if one of the two blogs can help
Hey @Anonymous ,
Go on your table first_of_mth and count each month nbr active customer from here:
Best regards,
@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 , refer if one of the two blogs can help
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 🙂
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
87 | |
84 | |
66 | |
49 |
User | Count |
---|---|
130 | |
110 | |
93 | |
70 | |
67 |