Forum Discussion

Frequent Visitor

4 years ago

Solved

DAX - count between 2 dates

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...

Need Help

BA_Pete
4 years ago
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

BA_Pete

Super User

3 years ago

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

Advocate I

3 years ago

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
Super User
3 years ago

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
BA_Pete
Super User
3 years ago

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

Advocate I

3 years ago

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.

Yanant1020

Advocate I

3 years ago

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

Super User

3 years ago

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

Advocate I

3 years ago

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.

Forum Discussion

DAX - count between 2 dates

Related content

Counting nulls

End date count

Running Count in DAX Sorted by Date

Counting consecutive dates

Count Of Drivers