Calculating number of weeks between 2 columns

[GabrielSantos]

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!

[malagari]

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.

[GabrielSantos]

@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.

Link to the pbix file.

Thanks again!

[GabrielSantos]

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

[Vvelarde · Accepted solution]

@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-.

[GabrielSantos]

@Vvelarde @malagari

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?

[malagari]

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.

[malagari]

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.

[GabrielSantos]

@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!

[malagari]

@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.