DAX code to calculate number of customerships

[Julkor]

Hi,

I have three different tables and I try to count number of customers. I would also want filter customers with sex, age and addresses. I would want to see what kind of people have been customers at some year, month etc. Somehow it doesn't work... 

I have these kinds of tables: 

Columns in table Customer: Unique customer key, birthday, sex

Columns in table Customership: Customer key, Start date of customership, End date of customership

Columns in table Address: Customer key, address information, Start date of address, End date of address

+ Two tables Agegroups and All_postcodes which includes list of ages and postcodes. 

 Tables Customer-Customership has relation with Customer key (1-*) and also tables Customer-Address (1-*). When I calculate number of customerships with code below, DAX works but filter Address not work. If I change to COUNT(Customership[Id])-calculation to Customer[Id], it doesn't work at all. What can I do with the code? 

My code now:

Number of customership =

VAR EndDatePerVisual = MAX(Calendar[Date])

VAR RESULT =

CALCULATE(

    count(Customership[Id]),

    REMOVEFILTERS(Calendar),

    Customership[Customership_Startdate] <= EndDatePerVisual,

    Customership[Customership_Enddate] >= EndDatePerVisual,

    DATEDIFF(Customer[Birthday],EndDatePerVisual,YEAR) IN VALUES(Agegroups[Age])),

    Address[Address_Startdate] <= EndDatePerVisual,

    Address[Address_Enddate] > EndDatePerVisual,

    Address[Postcode] IN VALUES(All_postcodes[Postcode])

RETURN RESULT

Thank you in advance

[Julkor]

Hi @v-zhangti,

There is an example of data below. Do you also want it as an Excel file? 

In the best case, I expect to make a line chart which shows the number of customers and date is in x-axis (for example: 1.2.2020 we had 100 customers, 1.3.2020 we had 110 customers(/customerships)). Then I could also filter for example 20-29 year old women who lived in Paris (women who were 20-29y in that date in a-axis and lived in Paris then) and see how much customers we had in different dates using just that filtering. Is that somehow possible to do? I guess that my code not work now because there are not relation between customership and address. (Sorry I change table names a little.)

Thank you for your time

[v-zhangti · Accepted solution]

Hi, @Julkor 

You can try the following methods.

Number of customership = 
VAR EndDatePerVisual = MAX(Calendar[Date])
VAR RESULT =
CALCULATE( DISTINCTCOUNT(Customership[Id]),FILTER(ALL(Customership),
    Customership[Customership_Startdate] <= EndDatePerVisual
    &&Customership[Customership_Enddate] >= EndDatePerVisual),
    FILTER(ALL('Address start and end'),'Address start and end'[Address_startdate]<= EndDatePerVisual
    &&'Address start and end'[Address_enddate] > EndDatePerVisual
    &&'Address start and end'[Postalcode] IN VALUES('Address'[Postalcode])))
RETURN 
RESULT

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-zhangti]

Hi, @Julkor 

Can you provide sample data for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures or Excel. I look forward to your response.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Julkor]

Hi @v-zhangti,

Was the data I sent helpful?