Creating a measure that looks at two date columns

[Bfaws]

Hello, 

I hope someone can help.

I have a list of employees who have allowances.  It essenstially shows the start date and the end date.  If the end date if open ended it shows as 31/12/2099.  What i would liek to know is how many employees had an allowances at a specific point in time. Looking at the raw data below:

I expect to see the following:

Year                    Number employees who had allowance

2021                   2    (kate bishop & kyle sinclare as both allowances were active in 2021)

2020                   2    (Kate Bishop & John Smith as both allowances were active in 2020)

2019                   1    (only john smith has an active allowance in 2019)

Please see link below for .pbix file with raw data and calendar table.

Any help would be much appeciated.

Thanks, 

Brendan

Click here for file 

[Bfaws]

@DataInsights 

Many thanks - this is very helpful and has solved my issue.

Thanks, 

Brendan

[DataInsights]

@Bfaws,

Try these measures. I created the table SlicerYear since the example Calendar table doesn't contain 2021.

Employees with Allowance Calc = 
VAR vYear =
    MAX ( SlicerYear[Year] )
VAR vResult =
    SUMX (
        Allowances,
        VAR vYearFrom =
            YEAR ( Allowances[Date from] )
        VAR vYearTo =
            YEAR ( Allowances[Date to] )
        RETURN
            IF ( vYear >= vYearFrom && vYear <= vYearTo, 1 )
    )
RETURN
    vResult

Employees with Allowance = 
// Calculates totals
SUMX ( VALUES ( SlicerYear[Year] ), [Employees with Allowance Calc])

Use SlicerYear[Year] in a visual:

[Bfaws]

@DataInsights 

Many thanks for this - its looks like it could solve my issue (and many others).  Unfortunately I cant replicate it exactly.  I think the issue is I can`t create your Year Slicer.  I have updated my calendar table to include 2021, however, when doing this the data for 2021 is not showing - although 2021 is now present in the table.

Link to .pbix file. 

I`d really appreciate it if you could have a look for me.

Many thanks.

[DataInsights · Accepted solution]

@Bfaws,

The latest date in your calendar table is 19-May-2020. The visual uses Year from the date hierarchy of Date. It's best practice to mark your calendar table as a date table (see link below). This will remove the automatic date hierarchy for each date field. You can create a calculated column for Year using the YEAR function.

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/