topic period between two dates considering the date-filter in DAX Commands and Tips

period between two dates considering the date-filter

PBI_alma — Thu, 07 Sep 2023 12:33:09 GMT

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

Re: period between two dates considering the date-filter

some_bih — Thu, 07 Sep 2023 13:21:52 GMT

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)

Re: period between two dates considering the date-filter

PBI_alma — Thu, 07 Sep 2023 14:54:41 GMT

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!

Re: period between two dates considering the date-filter

some_bih — Fri, 08 Sep 2023 06:30:16 GMT

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