Filter a Date but showing other dates on a visual

[Mohamed_59]

Hello guys,

I need your help please 🙂

Let's consider the following sample :

CustomerDate_EntranceDate_LeftAdress

CustomerDate_EntranceDate_LeftAdress

CUST_1	15/04/2022	28/08/2022	Adress_1
CUST_1	15/04/2022	28/08/2022	Adress_2
CUST_2	08/02/2022	01/09/2022	Adress_1
CUST_3	10/02/2022	06/12/2022	Adress_1
CUST_4	15/01/2022	30/06/2022	Adress_1
CUST_5	15/03/2022	30/06/2022	Adress_1
CUST_5	15/03/2022	30/06/2022	Adress_2
CUST_6	05/01/2022	02/12/2022	Adress_1
CUST_7	08/01/2022	31/12/2022	Adress_1
CUST_8	12/01/2022	18/09/2022	Adress_1
CUST_9	04/03/2022	09/09/2022	Adress_1
CUST_10	05/03/2022		Adress_1
CUST_11	06/03/2022		Adress_1
CUST_12	07/03/2022		Adress_1
CUST_13	08/03/2022		Adress_1
CUST_14	09/03/2022		Adress_1
CUST_14	09/03/2022		Adress_2

(this result above is not what I want)

Is there a way to display on the visual the customer who have been hired on a particular month

AND

showing them with the date they have left ?

For exemple, if I select on the filter : march 2022

I should see on the visual 5 customers hired on march 2022 who are still in the company

; 1 customer on june 2022 who have left

; 1 customer on september 2022 who have left

(which is not what you see on my visual above)

Thank you !

[v-yiruan-msft · Accepted solution]

Hi @Mohamed_59 ,

I create a sample pbix file(see the attachment), please check if that is what you want. 

1. Do not create any relationship between the table Dim_Date and Customers

2. Create two measures as below to get the count of customers for in and out

Nb of customers IN = 
VAR _selym =
    SELECTEDVALUE ( 'Dim_Date'[YearMonth] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Customer[Customer] ),
        FILTER (
            'Customer',
            YEAR ( 'Customer'[Date_Entrance] ) & FORMAT ( 'Customer'[Date_Entrance], "MM" ) = _selym
                && (
                    'Customer'[Date_Left] >= TODAY ()
                        || ISBLANK ( 'Customer'[Date_Left] )
                )
        )
    )

Nb of customers OUT = 
VAR _selym =
    SELECTEDVALUE ( 'Dim_Date'[YearMonth] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Customer[Customer] ),
        FILTER (
            'Customer',
            YEAR ( 'Customer'[Date_Entrance] ) & FORMAT ( 'Customer'[Date_Entrance], "MM" ) = _selym
                && YEAR ( 'Customer'[Date_Left] ) & FORMAT ( 'Customer'[Date_Left], "MM" )
                    = SELECTEDVALUE ( 'Customer'[LYM] )
                && NOT ( ISBLANK ( 'Customer'[Date_Left] ) )
                    && 'Customer'[Date_Left] < TODAY ()
        )
    )

3. Create a visual as below

If the above one can't help you get the expected result, please provide more raw data in your table 'Customers' (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Mohamed_59]

Hello @v-yiruan-msft 

It Works for me.

Thank you for your help !

[TomMartens]

Hey @Mohamed_59 ,

this is not possible!

If you are filtering the dates visible on a chart by using a slicer, the axis content can not be expanded.

You have to use a second date table used on the visual that is not related to the data or to the fact table. This "concept" is called the disconnected table. If you are using the search engine of your choice, you will find many examples.

Hopefully, this provides an idea of how to tackle your challenge.

Regards,

Tom