Forum Discussion

phil91's avatar
phil91
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's avatar
    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's avatar
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

Yanant1020's avatar
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's avatar
    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's avatar
    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.