cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Resolver I

## Calculating number of weeks between 2 columns

I've utilized the search function here and have found many similar posts/solutions, but not one that addresses the complexity of the calculation I'm trying to achieve.

I have a data set that includes start dates and departure dates. I'm looking to determine a moving length of service calculation. For example, if an employee was hired in January 2015, then he would have had 1 year of service in January 2016, and 2 years of service in January 2017.

I'd like the correct calculation to appear based on a Year slicer, so that the end user can see the tenure of the workforce looking back in time (they would see the employee as having just one year of service in 2015, two in 2016, and three in 2017).

I've attached a link to a data set with anonymized data.

I appreciate any input!

1 ACCEPTED SOLUTION
Community Champion

@GabrielSantos

Hi,

Try this:

Without a Relationship between DateTable & NamelyReport.

```Days of Service =
VAR StartDateorMinDate =
VALUES ( 'Namely Report'[Start date] )
VAR EndDateorMaxDate =
IF (
MIN ( MAX ( 'Namely Report'[Departure date] ), MAX ( DateTable[Date] ) )
<> BLANK (),
MIN ( MAX ( 'Namely Report'[Departure date] ), MAX ( DateTable[Date] ) ),
IF (
( TODAY () ) > DATE ( YEAR ( MAX ( DateTable[Date] ) ), 12, 31 ),
MAX ( DateTable[Date] ),
TODAY ()
)
)
RETURN
IF (
StartDateorMinDate > MAX ( DateTable[Date] )
|| EndDateorMaxDate < MIN ( DateTable[Date] ),
BLANK (),
DATEDIFF ( StartDateorMinDate, EndDateorMaxDate, DAY )
)```

Let me know if you need more help-.

Lima - Peru
9 REPLIES 9
Responsive Resident

Looking at your dataset, you should be able to create a custom measure on the Namely Report table that looks something like this:

```Days of Service =

var StartDateOrMinDate = MIN( MIN('Namely Report'[Start date]), MIN(DateTable[Date]) )
var EndDateOrMaxDate = MIN( MAX('Namely Report'[Departure date]), MAX(DateTable[Date]) )

return
CALCULATE( DATEDIFF(StartDateOrMinDate, EndDateOrMaxDate, DAY) )```

Then, when you add this to a table view with an employee identifier (email address, employee number, etc) and add a date slicer on the report page, you should be able to dynamically see how many days that employee has been with the company.

Dan Malagari
Resolver I

@malagari, this is a great first step, but it appears that when I add this measure to the table (page 3), it filters the employees on the list by what appears to be their departure date because the active relationship between the date table and the fact table is the Departure date > Date.

I've tried a combination of things including:

```Days of Service =

var StartDateorMinDate = MIN(MIN('Namely Report'[Start date]),MIN(DateTable[Date]))
var EndDateorMaxDate = MIN(MAX('Namely Report'[Departure date]),MAX('Namely Report'[Departure date]))

return
CALCULATE(DATEDIFF(StartDateorMinDate,EndDateorMaxDate,DAY),FILTER(ALL(DateTable[Date]),StartDateorMinDate<=MIN(DateTable[Date])))```

and

```Days of Service =

var StartDateorMinDate = MIN(MIN('Namely Report'[Start date]),MIN(DateTable[Date]))
var EndDateorMaxDate = MIN(MAX('Namely Report'[Departure date]),MAX('Namely Report'[Departure date]))

return
CALCULATE(DATEDIFF(StartDateorMinDate,EndDateorMaxDate,DAY),FILTER(ALL(DateTable[Date]),DateTable[Date]<=MAX(DateTable[Date])))```

Ultimately, I need to include anyone that has a start date equal to or lesser than the selected year. The filter, due to the relationship with departure date being active, is only filtering to include employees that have departed in the selected year.

Thanks again!

Resolver I

Will be jumping back on this today, but would appreciate any second set of eyes!

Community Champion

@GabrielSantos

Hi,

Try this:

Without a Relationship between DateTable & NamelyReport.

```Days of Service =
VAR StartDateorMinDate =
VALUES ( 'Namely Report'[Start date] )
VAR EndDateorMaxDate =
IF (
MIN ( MAX ( 'Namely Report'[Departure date] ), MAX ( DateTable[Date] ) )
<> BLANK (),
MIN ( MAX ( 'Namely Report'[Departure date] ), MAX ( DateTable[Date] ) ),
IF (
( TODAY () ) > DATE ( YEAR ( MAX ( DateTable[Date] ) ), 12, 31 ),
MAX ( DateTable[Date] ),
TODAY ()
)
)
RETURN
IF (
StartDateorMinDate > MAX ( DateTable[Date] )
|| EndDateorMaxDate < MIN ( DateTable[Date] ),
BLANK (),
DATEDIFF ( StartDateorMinDate, EndDateorMaxDate, DAY )
)```

Let me know if you need more help-.

Lima - Peru
Resolver I

Appreciate both of your answers. I'm now confident that I will have this resolved today.

In the abstract, is there an instance in which having those relationships between the DateTable and NamelyReport would be beneficial/necessary? Perhaps only when I'm needing to do time intelligence calculations?

Responsive Resident

I've found that relationships between the Date Table and your fact table are only beneficial when you've got one date column you're working with, or you're relying heavily on Power BI's automatic aggregate measures (average of __, sum of __).  When I'm doing more intensive measure definitions, I find it easier to have the Date Table unrelated so I can apply it how I see fit for the report context.

One example that it might be useful is if you have a TransactionTable, with a [DateSold] column.  You may want to create a relationship between [Date] and [DateSold] in order to only show transactions that were within that date range.

Dan Malagari
Responsive Resident

Hey @GabrielSantos,

I forgot to mention in the first post - I believe I deleted the relationship between Departure Date and your DateTable.  I didn't see the need for it, unless you have some other calculation or visualization that it's based off of.

Dan Malagari
Resolver I

@malagari In effect, creating a disconnected slicer? I thought about that but wasn't entirely sure about the impact it would have on my other visualizations (on page 2).

I'll give it a try!

Responsive Resident

@GabrielSantos - Correct.  I've found that when I'm dealing with multiple different date values, I simply use the date table as reference, rather than trying to create and manage the relationships between the date table and all of the different date entries.

Dan Malagari

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors