Help writing DAX expression

[eyates]

Based on the following table, I need help writing a DAX expression showing when a location sk is active and not active.

Here is an example of a single location's (customer's) membership history in the 'Service Agreement (2)' table. 

I have a Calendar table that is connected to this table via Calendar[Calendar Date] -> Service Agreement (2)[Event Date]

Looking at 2022/2023 as an example, I want the end result to look like this.

How can I write a dax expression to show this location as active from 10/15/2022 to 1/11/2023?

[123abc]

To determine if a location is active for a specific period, you'll need to analyze the Service Agreement (2) table in relation to the Calendar table for the desired period. Based on your example, you want to see if a location is active between 10/15/2022 and 1/11/2023.

Here's a step-by-step guide to achieving this:

1. Create a calculated column in your Calendar table: This column will check for each date if there is any corresponding active location in the Service Agreement (2) table.

IsActive =
VAR CurrentDate = Calendar[Calendar Date]
RETURN
IF(
COUNTROWS(
FILTER(
'Service Agreement (2)',
CurrentDate >= 'Service Agreement (2)'[Start Date] &&
CurrentDate <= 'Service Agreement (2)'[End Date]
)
) > 0,
"Active",
"Not Active"
)

1. Use this calculated column in your report: After you create this calculated column in the Calendar table, you can use it in your report visuals. When you filter or drill down to the desired date range (e.g., from 10/15/2022 to 1/11/2023), this calculated column will show whether each location is active or not for each date within that range.

2. Show Start and End Dates for Active Period: If you want to display the start and end dates of the active period for each location, you'll need a more complex DAX measure. This measure can be used in a table visual to show the active period for each location:

ActivePeriod =
VAR CurrentDate = MAX(Calendar[Calendar Date])
RETURN
IF(
COUNTROWS(
FILTER(
'Service Agreement (2)',
CurrentDate >= 'Service Agreement (2)'[Start Date] &&
CurrentDate <= 'Service Agreement (2)'[End Date]
)
) > 0,
CALCULATE(
MIN('Service Agreement (2)'[Start Date]),
FILTER(
'Service Agreement (2)',
CurrentDate >= 'Service Agreement (2)'[Start Date] &&
CurrentDate <= 'Service Agreement (2)'[End Date]
)
)
& " to " &
CALCULATE(
MAX('Service Agreement (2)'[End Date]),
FILTER(
'Service Agreement (2)',
CurrentDate >= 'Service Agreement (2)'[Start Date] &&
CurrentDate <= 'Service Agreement (2)'[End Date]
)
),
BLANK()
)

This ActivePeriod measure will return the start and end dates of the active period for each location on the selected date from the Calendar table.

Remember to adjust table and column names according to your actual data model.