Forum Discussion
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 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
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
14 Replies
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
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)
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
