DAX - count between 2 dates

[phil91]

Hi,

Hoping somebody could help me with the below problem.

I have a HR table of employees 'Merged Starters and Leavers' containing a start date and leaving date.

I have a slicer on the report page from my date table where a time period can be selected in the format May 2021.

I then have the 2 measures below which take the start date and end date selected from the slicer

Maximum Date Selected Period = Max('Date Table'[Date]) +1

Minimum Date Selected Period = Min('Date Table'[Date])

I want to calculate the number of starters in a period, number of leavers in a period and number employed in a period (this therefore needs to reference start and end date). The number of starters and leavers are simple enough to calculate.

Which date from my fact table (start date/leaving date) should be the active relationship with the date table and how can I calculate the total number employed in the period selected (start date < Min && end date if not blank > max)

[BA_Pete · Accepted solution]

Hi @phil91 ,

In terms of the active relationship, I guess this would be personal preference to some degree. If your visuals most-frequently utilise metrics based on [start date], then make this one active and vice-versa. If there's no difference, then I tend to make them all inactive to avoid confusion later on.

Regarding number employed during the period, you'll need a value-over-time measure, something like this:

_noofEmployed = 
VAR date_to_examine =
MAX(calendar[date])
VAR noofEmployed =
CALCULATE(
    CALCULATE(
        DISTINCTCOUNT( yourTable[employeeCode]),
        KEEPFILTERS( date_to_examine >= yourTable[start date]),
        KEEPFILTERS( date_to_examine <= yourTable[leave date])
    ),
    CROSSFILTER(calendar[date], yourTable[relatedDateFieldIfUsed], None)
)
RETURN
    IF (ISBLANK(noofEmployed ), BLANK(), noofEmployed )

You'll notice that I've removed the crossfilter in this example as this works only when unrelated. If you make both of your relationships inactive, then you can remove the first CALCULATE and the CROSSFILTER line.

Pete

[Yanant1020]

This is the solution I have reached as well for a similar model. However, without being able to leverage actual relationships and instead rely on a "virtual" relationship, the calculations are very slow (30s+). I am curious if:

1. Anyone knows what this type of Fact table would be called in traditional datawarehouse-ing

2. Anyone has any tips for enhancing the performance of this measure (without blowing up the grain of the fact table to include every day)

[BA_Pete]

Hi @Yanant1020 ,

In terms of the type of fact table this is, it's actually closer to being a dimension table i.e. unique employee id values with additional information about each unique item (e.g. start date, end date etc.) assuming, of course, that if an employee leaves then restarts that they are assigned a new unique employee id.

If the table updates with duplicated id rows when new information is added (e.g. holiday start date, holiday end date) then this would likely be classed as a Slowly-Changing Dimension (SCD) table for the purposes of how you would manage relationships and calculations over it.

In terms of the performance of this measure within your environment, it's almost impossible to say what the issue is and how to improve it without far more information about your data model, the actual data/table it's to be applied over, and what your output requirements are.

Pete

[Yanant1020]

I agree in form it is more of a slowly changing dimension but in function it is a fact table. In my case and it sounds like the case of the original poster, the function of this table is to perform aggregations, primarily record counts. It even has related dimensions that you could consider snowflaked if you wanted to call it a SCD because of it's form or would be regular star schema dimensions if you wanted to call it a fact table. It is an interesting case because it sits between being a fact table and being a dimension. I think this is why the performance is rough and it relies on a "virtual" relationship with the > and < filtering around a selected date. I will read up on aggregations of slowly changing dimensions. I'm just thinking that before PBI, in standard datawarehousing, counting the number of records that classify as a certain status at a given date must've been a requirement. Maybe it is a blind spot in traditional star schemas. I've been so impressed by star schemas though that generally I've found they have names and solutions for almost all analytical data modelling. I just can't find any documentation on this case.

[BA_Pete]

I don't think it's a blind spot at all, you just need to select the correct solution for your environment and required output.

When you talk about "virtual relationships" what exactly do you mean? If you can give some detail around your current setup I'm happy to look at optimising for you - there's almost always something that can be done, especially if you're getting 30s visual load times, that's not normal at all.

Pete

[Yanant1020]

Thank you for your interest in continuing to help me on this. Here is an example of our fact table.

As you can see, it relates to multiple dimensions; however, it cannot have an established relationship with the date dimension as the records in this table are represented by date ranges. The measure pattern you supplied before works but even with this table as the fact in a pure star schema, we have performance issues. The primary measure we are experiencing this slow-ness with computes the count of customers in status 3 on the given date, the count of customers in status 3 3 years before the given date (period start), and divides the two. [Given date status 3 count]/[3 years prior status 3 count].

[BA_Pete]

Ok, so assuming that is the only fact table, I'd probably write your required measure like this:

_status3_pctChange =
VAR __currDate =
    MAX(calendar[date])
VAR __prevDate =
    DATE(
        YEAR(__currDate) -3,
        MONTH(__currDate),
        DAY(__currDate)
    )
VAR __currCount =
    CALCULATE(
        DISTINCTCOUNT(yourTable[Customer_Key]),
        FILTER(
            yourTable,
            yourTable[Status_Key] = 3
            && __currDate >= yourTable[Start Date]
            && ( __currDate < yourTable[End Date] || ISBLANK(yourTable[End Date]) )
        )
    )
VAR __prevCount =
    CALCULATE(
        DISTINCTCOUNT(yourTable[Customer_Key]),
        FILTER(
            yourTable,
            yourTable[Status_Key] = 3
            && __prevDate >= yourTable[Start Date]
            && ( __prevDate < yourTable[End Date] || ISBLANK(yourTable[End Date]) )
        )
    )
RETURN
DIVIDE(__currCount, prevCount, 0)

Obviously you'll want to avoid select 29th February as your target date, or you'll need to work around this.

Unless your table has 50M+ rows, you shouldn't have any significant performance issues with this. If you still do, then there could be something else going on. I'm also making the assumption that your data is imported, not Direct or Live query.

Let me know how it goes.

Pete

[Yanant1020]

Thank you again for your assistance on this. I found a workaround I implemented that I would call "less than ideal" because it uses an additional traditiional transaction level fact table for the active customer count calculation. Here are the measures I was originally using that resulted in the poor performance:

You can see they closely resemble the recommendation you sent, just broken into pieces. If I throw this final measure into a card, it evaluates very quickly. However, we have a requirement to show this calculation as a daily trend over the last 3 years. When I try to put this measure across a date axis in a line chart, it takes over 30 seconds to return a result and I have it filtered for 1 year, not 3.

Edit (some more info):

Peformance results for returning 10 day and 365 day trend:

Relevant portion of model:

Row Counts:

DIM - Customers: 552,477

DIM - Status: 6

FACT - Customer Status: 2,036,733

Thoughts:

I was reading through Kimball's datawarehouse toolkit yesterday for inspiration and found the aggregating snapshot fact table employed in a similar problem concerning inventory levels over time. They logged for each period (monthly, weekly, daily, whatever is required) what the inventory level was for each part at the end of that period. That could be applied to this case but it would result in the 2 million record status fact table to expand for each day the customer has been in our system. This is definitely possible and I can see where the dax performance would improve, I can also see that SQL would probably handle this expansion fairly easily, it just seems like so much data to store in an in memory database just for performance enhancements.

[BA_Pete]

Did you try my measure?

Whilst the theory of your solution resembles what I provided, there's a number of very key differences in the actual implementation. Please try it in its exact form and let me know how it performs.

Regarding the DWH option: I don't think this would be necessary at all. DAX over a Tabular model can chew up huge tables with immense speed and efficiency. Your fact table of 2M rows is relatively small in the context of DAX/Tabular, which can handle billions of rows with the correct implementation.

Pete

[Yanant1020]

I had to slightly modify your function because the "status = 3" filter I sent originally isn't a fair representation of the data. In actuality the Status dimension is a hierarchical dimension that relates to the fact table through the child status and I need to filter on the parent status. Here is the function I wrote:

Pete to the Rescue = 
VAR __currDate =
    MIN('DIM - Date'[Full Date])
VAR __prevDate =
    DATE(
        YEAR(__currDate) -3,
        MONTH(__currDate),
        DAY(__currDate)
    )
VAR __currCount =
    CALCULATE(
        DISTINCTCOUNT('FACT - Customer Status'[Account Number]),
        FILTER(
            'FACT - Customer Status',
            RELATED('DIM - Status'[Status]) = "Active"
            && __currDate >= 'FACT - Customer Status'[Start Date]
            && __currDate < 'FACT - Customer Status'[End Date]
        )
    )
VAR __prevCount =
    CALCULATE(
        DISTINCTCOUNT('FACT - Customer Status'[Account Number]),
        FILTER(
            'FACT - Customer Status',
            RELATED('DIM - Status'[Status]) = "Active"
            && __prevDate >= 'FACT - Customer Status'[Start Date]
            && __prevDate < 'FACT - Customer Status'[End Date]
        )
    )
RETURN
DIVIDE(__currCount, __prevCount, 0)

And here are the results:

Your measure has a significant improvement in performance over what I had written, but it is still far too slow (Just over 2 minutes to calculate 1 year). Please let me know if you believe any of the modifications I made to your measure are the cause or if there is another measure design you can think of.

Also, here are the results if the new measure is the only one on the page, just to be sure there isn't some kind of cross-dependency occurring. It went down to just over 90 seconds.

[BA_Pete]

Firstly, I love the measure name! I'm going to be updating all my models with a similar naming convention, just so everyone understands how it be 😂

Secondly, on the DAX performance analyser, you should be expanding the results using the little '+' icon. This will tell you exactly whether it's the DAX query, the displaying of the visual, or processor wait time that's causing the issue.

One of the key things in my solution was the removal of the relationship call to the dimension table. You should be able to improve what you have by removing the RELATED functions. You already have an active ONE:MANY relationship in place, and you're not trying to actually bring any values across the relationship, so you shouldn't need this at all. Just 'DIM - Status'[Status] = "Active" OUTSIDE the FILTER function but inside the CALCULATE should work fine (and hopefully a bit faster).

Just out of interest, can you run my measure it in its original form please? I know it won't give you the output you want, but I'd be interested to see the impact of the relationship call on the query time. If this has a significant impact, then you may want to look into bringing your additional dimension level into the fact table.

I also alluded to my assumption that your model is an IMPORT model, not Direct or Live query. Can you confirm this is the case?

Pete

[Yanant1020]

Great point on the calculate, I don't know how I overlooked that. As for the naming convention, you have to give credit where credit is due. I have added three more versions of the original measure you wrote.

1. V1 - same as yesterday

2. V2 - Closer to the way you wrote it originally, but uses an IN to include all the relevant status details

3. V2 (only one detail) - exactly as you wrote it, but it only filters for one status detail so it is not the same calculation as the other measures

4. V3 - same as yesterday but moving the status filter to the calculate.

Here are the measures:

Pete to the Rescue - v1 = 
VAR __currDate =
    MIN('DIM - Date'[Full Date])
VAR __prevDate =
    DATE(
        YEAR(__currDate) -3,
        MONTH(__currDate),
        DAY(__currDate)
    )
VAR __currCount =
    CALCULATE(
        DISTINCTCOUNT('FACT - Customer Status'[Account Number]),
        FILTER(
            'FACT - Customer Status',
            RELATED('DIM - Status'[Status]) = "Active"
            && __currDate >= 'FACT - Customer Status'[Start Date]
            && __currDate < 'FACT - Customer Status'[End Date]
        )
    )
VAR __prevCount =
    CALCULATE(
        DISTINCTCOUNT('FACT - Customer Status'[Account Number]),
        FILTER(
            'FACT - Customer Status',
            RELATED('DIM - Status'[Status]) = "Active"
            && __prevDate >= 'FACT - Customer Status'[Start Date]
            && __prevDate < 'FACT - Customer Status'[End Date]
        )
    )
RETURN
DIVIDE(__currCount, __prevCount, 0)

Pete to the Rescue - v2 = 
VAR __currDate =
    MIN('DIM - Date'[Full Date])
VAR __prevDate =
    DATE(
        YEAR(__currDate) -3,
        MONTH(__currDate),
        DAY(__currDate)
    )
VAR __currCount =
    CALCULATE(
        DISTINCTCOUNT('FACT - Customer Status'[Account Number]),
        FILTER(
            'FACT - Customer Status',
            'FACT - Customer Status'[Status] IN {"New Customer", "Reanimated Customer", "Active Customer - New", "Active Customer - Reanimated"}
            && __currDate >= 'FACT - Customer Status'[Start Date]
            && __currDate < 'FACT - Customer Status'[End Date]
        )
    )
VAR __prevCount =
    CALCULATE(
        DISTINCTCOUNT('FACT - Customer Status'[Account Number]),
        FILTER(
            'FACT - Customer Status',
            'FACT - Customer Status'[Status] IN {"New Customer", "Reanimated Customer", "Active Customer - New", "Active Customer - Reanimated"}
            && __prevDate >= 'FACT - Customer Status'[Start Date]
            && __prevDate < 'FACT - Customer Status'[End Date]
        )
    )
RETURN
DIVIDE(__currCount, __prevCount, 0)

Pete to the Rescue - v2 (only one detail) = 
VAR __currDate =
    MIN('DIM - Date'[Full Date])
VAR __prevDate =
    DATE(
        YEAR(__currDate) -3,
        MONTH(__currDate),
        DAY(__currDate)
    )
VAR __currCount =
    CALCULATE(
        DISTINCTCOUNT('FACT - Customer Status'[Account Number]),
        FILTER(
            'FACT - Customer Status',
            'FACT - Customer Status'[Status] = "Active Customer - New"
            && __currDate >= 'FACT - Customer Status'[Start Date]
            && __currDate < 'FACT - Customer Status'[End Date]
        )
    )
VAR __prevCount =
    CALCULATE(
        DISTINCTCOUNT('FACT - Customer Status'[Account Number]),
        FILTER(
            'FACT - Customer Status',
            'FACT - Customer Status'[Status] = "Active Customer - New"
            && __prevDate >= 'FACT - Customer Status'[Start Date]
            && __prevDate < 'FACT - Customer Status'[End Date]
        )
    )
RETURN
DIVIDE(__currCount, __prevCount, 0)

Pete to the Rescue - v3 = 
VAR __currDate =
    MIN('DIM - Date'[Full Date])
VAR __prevDate =
    DATE(
        YEAR(__currDate) -3,
        MONTH(__currDate),
        DAY(__currDate)
    )
VAR __currCount =
    CALCULATE(
        DISTINCTCOUNT('FACT - Customer Status'[Account Number]),
        FILTER(
            'FACT - Customer Status',
            __currDate >= 'FACT - Customer Status'[Start Date]
            && __currDate < 'FACT - Customer Status'[End Date]
        ),
        'DIM - Status'[Status] = "Active"
    )
VAR __prevCount =
    CALCULATE(
        DISTINCTCOUNT('FACT - Customer Status'[Account Number]),
        FILTER(
            'FACT - Customer Status',
            __prevDate >= 'FACT - Customer Status'[Start Date]
            && __prevDate < 'FACT - Customer Status'[End Date]
        ),
        'DIM - Status'[Status] = "Active"
    )
RETURN
DIVIDE(__currCount, __prevCount, 0)

And here are the results:

If I find the time I will try to run the test in isolation so there isn't any kind of resource contention going on but in reality, the report will have more than this one visual on the page so some resource contention is probably more real to life. I am using import mode.

Sticking as close as possible to the measure you originally recommended does result in the fastest processing time, but it is still over a minute.  Using the additional fact table I still don't have great performance, but it took 9 seconds in my test. 9 seconds is clearly better but it comes with the addition of a few million record fact table. Having an additional fact table means more system load and potential inconsistency between the fact tables. I would strongly prefer to use the status fact table solely.

[BA_Pete]

Ok, let's focus on [Pete to the Rescue - v2] as this gives you what you want, and is the fastest out of all that do give you that (I'm surprised that RELATED is faster than separated dimension TBH, but there we are).

Let's break it up to see if we can find the culprit:

PttRv2_1 = 
VAR __currDate =
    MIN('DIM - Date'[Full Date])
RETURN
    CALCULATE(
        DISTINCTCOUNT('FACT - Customer Status'[Account Number]),
        FILTER(
            'FACT - Customer Status',
            'FACT - Customer Status'[Status] IN {"New Customer", "Reanimated Customer", "Active Customer - New", "Active Customer - Reanimated"}
            && __currDate >= 'FACT - Customer Status'[Start Date]
            && __currDate < 'FACT - Customer Status'[End Date]
        )
    )

PttRv2_2 = 
VAR __currDate =
    MIN('DIM - Date'[Full Date])
VAR __prevDate =
    DATE(
        YEAR(__currDate) -3,
        MONTH(__currDate),
        DAY(__currDate)
    )
RETURN
    CALCULATE(
        DISTINCTCOUNT('FACT - Customer Status'[Account Number]),
        FILTER(
            'FACT - Customer Status',
            'FACT - Customer Status'[Status] IN {"New Customer", "Reanimated Customer", "Active Customer - New", "Active Customer - Reanimated"}
            && __prevDate >= 'FACT - Customer Status'[Start Date]
            && __prevDate < 'FACT - Customer Status'[End Date]
        )
    )

Pete

[Yanant1020]

Ok, fair warning, this is going to be a large measure dump. I went down this path before, isolating each side of the division and seeing if I can't get one of them to run quickly. What you have as PttRv2_1 is what I am calling [Active Customer Count - X Period End] while PttRv2_2 is [Active Customer Count - X Period Start]. Not great names but they work for now. I noticed before that [Active Customer Count - X Period Start] was slower so I started by trying to optimize this one.

First, your two measures, plus one tweak I added (countrows instead of distinctcount())

PttRv2_1 = 
VAR __currDate =
    MIN('DIM - Date'[Full Date])
RETURN
    CALCULATE(
        DISTINCTCOUNT('FACT - Customer Status'[Account Number]),
        FILTER(
            'FACT - Customer Status',
            'FACT - Customer Status'[Status] IN {"New Customer", "Reanimated Customer", "Active Customer - New", "Active Customer - Reanimated"}
            && __currDate >= 'FACT - Customer Status'[Start Date]
            && __currDate < 'FACT - Customer Status'[End Date]
        )
    )

PttRv2_1_Countrows = 
VAR __currDate =
    MIN('DIM - Date'[Full Date])
RETURN
    CALCULATE(
        COUNTROWS('FACT - Customer Status'),
        FILTER(
            'FACT - Customer Status',
            'FACT - Customer Status'[Status] IN {"New Customer", "Reanimated Customer", "Active Customer - New", "Active Customer - Reanimated"}
            && __currDate >= 'FACT - Customer Status'[Start Date]
            && __currDate < 'FACT - Customer Status'[End Date]
        )
    )

PttRv2_2 = 
VAR __currDate =
    MIN('DIM - Date'[Full Date])
VAR __prevDate =
    DATE(
        YEAR(__currDate) -3,
        MONTH(__currDate),
        DAY(__currDate)
    )
RETURN
    CALCULATE(
        DISTINCTCOUNT('FACT - Customer Status'[Account Number]),
        FILTER(
            'FACT - Customer Status',
            'FACT - Customer Status'[Status] IN {"New Customer", "Reanimated Customer", "Active Customer - New", "Active Customer - Reanimated"}
            && __prevDate >= 'FACT - Customer Status'[Start Date]
            && __prevDate < 'FACT - Customer Status'[End Date]
        )
    )

PttRv2_2_Countrows = 
VAR __currDate =
    MIN('DIM - Date'[Full Date])
VAR __prevDate =
    DATE(
        YEAR(__currDate) -3,
        MONTH(__currDate),
        DAY(__currDate)
    )
RETURN
    CALCULATE(
        DISTINCTCOUNT('FACT - Customer Status'[Account Number]),
        FILTER(
            'FACT - Customer Status',
            'FACT - Customer Status'[Status] IN {"New Customer", "Reanimated Customer", "Active Customer - New", "Active Customer - Reanimated"}
            && __prevDate >= 'FACT - Customer Status'[Start Date]
            && __prevDate < 'FACT - Customer Status'[End Date]
        )
    )

Results:

And here are all the versions I tried of [Active Customer Count - X Period Start]. They range in performance from "oh, it's still running" to "something's wrong":

Active Customers - X Period Start - v2 = 
VAR _PeriodYears = [Period Length Value]
VAR _PeriodStartDate =
    DATE ( YEAR ( [Selected Date] ) - _PeriodYears, MONTH ( [Selected Date] ), DAY ( [Selected Date] ) )
RETURN
    CALCULATE (
        COUNTROWS (
            FILTER (
                CALCULATETABLE (
                    SUMMARIZE (
                        'FACT - Customer Status',
                        'FACT - Customer Status'[Account Number],
                        "Status",
                            CALCULATE (
                                VALUES ( 'DIM - Status'[Status] ),
                                TOPN (
                                    1,
                                    SUMMARIZE (
                                        'FACT - Customer Status',
                                        'DIM - Status'[Status],
                                        'DIM - Date'[Full Date]
                                    ),
                                    'DIM - Date'[Full Date], DESC
                                )
                            )
                    ),
                    DATESBETWEEN (
                        'DIM - Date'[Full Date],
                        BLANK (),
                        MIN ( 'DIM - Date'[Full Date] )
                    ),
                    USERELATIONSHIP ( 'DIM - Date'[Date_Key], 'FACT - Customer Status'[StatusStartDate_Key] )
                ),
                [Status] = "Active"
            )
        ),
        'DIM - Date'[Full Date] = _PeriodStartDate
    )

Active Customers - X Period Start - v3 = 
VAR _PeriodYears = [Period Length Value]
VAR _PeriodStartDate =
    DATE ( YEAR ( [Selected Date] ) - _PeriodYears, MONTH ( [Selected Date] ), DAY ( [Selected Date] ) )
RETURN
COUNTROWS(
    FILTER (
        GENERATE (
            SUMMARIZE (
                'FACT - Customer Status',
                'DIM - Status'[Status],
                'FACT - Customer Status'[Start Date],
                'FACT - Customer Status'[End Date]
            ),
            DATESBETWEEN (
                'DIM - Date'[Full Date],
                'FACT - Customer Status'[Start Date],
                'FACT - Customer Status'[End Date]
            )
        ),
        [Full Date] = _PeriodStartDate
        && [Status] = "Active"
    )
)
Active Customers - X Period Start - v4 = 
VAR _PeriodYears = [Period Length Value]
VAR _PeriodStartDate =
    DATE ( YEAR ( [Selected Date] ) - _PeriodYears, MONTH ( [Selected Date] ), DAY ( [Selected Date] ) )
RETURN
    CALCULATE (
        COUNTROWS (
            FILTER (
                CALCULATETABLE (
                    SUMMARIZE (
                        'FACT - Customer Status',
                        'FACT - Customer Status'[Account Number],
                        "Status",
                            CALCULATE (
                                VALUES ( 'DIM - Status'[Status] ),
                                TOPN (
                                    1,
                                    SUMMARIZE (
                                        'FACT - Customer Status',
                                        'DIM - Status'[Status],
                                        'FACT - Customer Status'[Start Date]
                                    ),
                                    'FACT - Customer Status'[Start Date], DESC
                                )
                            )
                    ),
                    DATESBETWEEN (
                        'DIM - Date'[Full Date],
                        BLANK (),
                        MIN ( 'DIM - Date'[Full Date] )
                    ),
                    USERELATIONSHIP ( 'DIM - Date'[Date_Key], 'FACT - Customer Status'[StatusStartDate_Key] )
                ),
                [Status] = "Active"
            )
        ),
        'DIM - Date'[Full Date] = _PeriodStartDate
    )
Active Customers - X Period Start - v5 = 
VAR _PeriodYears = [Period Length Value]
VAR _PeriodStartDate =
    DATE ( YEAR ( [Selected Date] ) - _PeriodYears, MONTH ( [Selected Date] ), DAY ( [Selected Date] ) )
RETURN
    CALCULATE (
        COUNTROWS (
            FILTER (
                CALCULATETABLE (
                    SUMMARIZE (
                        'FACT - Customer Status',
                        'FACT - Customer Status'[Account Number],
                        "Status",
                            CALCULATE (
                                VALUES ( 'DIM - Status'[Status] ),
                                TOPN (
                                    1,
                                    SUMMARIZE (
                                        'FACT - Customer Status',
                                        'DIM - Status'[Status],
                                        'FACT - Customer Status'[Start Date]
                                    ),
                                    'FACT - Customer Status'[Start Date], DESC
                                )
                            )
                    ),
                    'FACT - Customer Status'[Start Date] < _PeriodStartDate
                ),
                [Status] = "Active"
            )
        )
    )

Active Customers - X Period Start - v6 = 
VAR _PeriodYears = [Period Length Value]
VAR _PeriodStartDate = DATE(YEAR([Selected Date]) - _PeriodYears, MONTH([Selected Date]), DAY([Selected Date]))
RETURN
    CALCULATE (
        COUNTROWS ( 'FACT - Customer Status' ),
        'FACT - Customer Status'[Start Date] < _PeriodStartDate,
        'FACT - Customer Status'[End Date] > _PeriodStartDate,
        'DIM - Status'[Status] = "Active"
    )

I can update this message with the results of the second set of measures later, but I have a meeting now. Thanks again for all the help.