cancel
Showing results for
Did you mean:
Helper III

## Creating a measure that looks at two date columns

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

1 ACCEPTED SOLUTION
Super User

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.

Proud to be a Super User!

4 REPLIES 4
Helper III

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

Thanks,

Brendan

Super User

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:

Proud to be a Super User!

Helper III

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.

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

Many thanks.

Super User

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.

Proud to be a Super User!

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors