Forum Discussion

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

Yanant1020's avatar
Yanant1020
Advocate I
3 years ago

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