Problem with date range

[Syndicate_Admin]

Good morning.

I have a mess, I hope you can help me solve.

Context: I have 2 tables, 1 with services given to a client (food, drink, hotel, car, etc.), and the other with the client's status (solvent, delinquent, overdrawn, new), these statuses can last for some time, that is, they have start_date and have end_date.

Problem:
I need to know the status that the client had when receiving the service. But since it is a range, when I select a month I get only those that had change that month.

ID	Start_date	END_DATE	STATUS
CLI001	01/01/2022	10/03/2022	SOLVENT
CLI001	15/02/2022	25/04/2022	DEFAULTING
CLI001	05/05/2022	10/07/2022	SOBREGIRADO
CLI004	01/01/2022	01/02/2022	NEW

Suppose that cli001 received services on 02/25/2022 and 06/16/2022, both dates correspond to different ranges, but how can I capture them?

Note: I cannot "fill" the day-to-day date ranges with the missing dates because the date ranges are huge and the number of clients exceeds 2 thousand.
Although it gets heavy, I was able to join the 2 tables by the ID, but I have not been able to determine what status I had when I received the service...

Thanks in advance...

[Syndicate_Admin]

Fix it this way:

MODALITY RESULT =

IF(START_DATE > STATUS_DATE,

"N/A",

//else

IF(STATUS_DATE >= START_DATE

&& STATUS_DATE <= END_DATE,

"APPLY","N/A"))

[v-zhangti]

Hi, @Syndicate_Admin 

You can try the following methods.
New table:

Date = CALENDAR(MIN('Table'[Start_date]),MAX('Table'[END_DATE]))

Measure =
IF ( SELECTEDVALUE ( 'Date'[Date] ) = BLANK (), 1,
    IF ( SELECTEDVALUE ( 'Date'[Date] ) >= SELECTEDVALUE ( 'Table'[Start_date] )
            && SELECTEDVALUE ( 'Date'[Date] ) <= SELECTEDVALUE ( 'Table'[END_DATE] ),
        1,
        0
    )
)

 Put measure in the filter of this view and set it equal to 1.

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.