period between two dates considering the date-filter

[PBI_alma]

Hello all,

I have an issue that I hope you can help me with your expertise.

There is a table with the following columns and --> examples:

- Customer_ID --> ABC
- Start Status green as date1 -->2023-07-25
- End Status green as date2 --> 2023-09-05
- Activity Code --> 10

Now I would like to build a report that uses a period filter to see how many customers had status green and this or that activity code at some point in the period.

I have built in here two view period filters (date1, date 2) to define the period. --> 2023-08-01 - 2023-08-31

How do I write the DAX query to take into account each filter setting and get all the customers whose start date may also have been before or whose end date may have been after the consideration period? In the example, customer ABC would have had status green in the period, but would not be included in a simple filter setting.

Do you have any ideas how to solve this?

= calculate(Distinctcount(Customer_ID),....??

Thank you, and best regards

Alma

[some_bih]

Hi @PBI_alma 

From your example, I inserted into Excel and created measure, and Create Calendar / Date Table

Did I answer your question? Kudos appreciated / accept solution!

Number Customers with Green Status =

VAR __start_date = MIN('Date'[Date])

VAR _end_date = MAX('Date'[Date])

VAR __selected_activity_code = SELECTEDVALUE(Sheet1[Activity Code])

RETURN

COUNTROWS(

    FILTER(

        'Sheet1',

        'Sheet1'[Activity Code] = __selected_activity_code &&

        (

            'Sheet1'[Start Status green] <= _end_date &&

            'Sheet1'[End Status green] >= __start_date

        )

    )

)

Output, July in period selected (1 customer)

Output, October in period selected (none customer)

[PBI_alma]

Hi,

thanks a lot! this looks very good. But I am sorry that I forgot to mention that the customer IDs are not distinct in the list, so I have to count the distinct IDs. I tried to include calculate(distinctcount( in the code, but it doesnt work - could you please help me one more time, in which part of the code I can include the distinct count? Thank you so much!

[some_bih · Accepted solution]

Hi @PBI_alma 

Number Customers with Green Status v2 =

VAR __start_date = MIN('Date'[Date])

VAR _end_date = MAX('Date'[Date])

VAR __selected_activity_code = SELECTEDVALUE(Sheet1[Activity Code])

RETURN

CALCULATE(

    DISTINCTCOUNT(Sheet1[Customer_ID]),--calculation of distinct count for Customer ID

    FILTER(

        'Sheet1',

        'Sheet1'[Activity Code] = __selected_activity_code &&

        (

            'Sheet1'[Start Status green] <= _end_date &&

            'Sheet1'[End Status green] >= __start_date

        )

    )

)

Did I answer your question? Kudos appreciated / accept solution!

 

Output, with additional dummy data