RunningTotal with Userelationship

[PoweredOut]

Hello

I have a table with HireDate and TerminationDate and both dates have an unactive relationship with the DateTable. An active relationship isn't possible.

I would like to create a running total of employees employed during this timeframe.

Example: Employee 1 started on Jan 1st 2020 and left the company on September 30th 2022. I would like to have a running total between those dates. In this case it will be always 1, but it fulfills my purpose of populating a visual on all data hierarchies. Similar to below, but also integrating the TerminationDate as the final date

Employed Duration = 
VAR MaxDate = Max('DIM Date'[Date])

Return

Calculate(
    [Emploeyee],
    USERELATIONSHIP('Employee'[HireDate], 'DIM Date'[Date]), 'DIM Date'[Date] <= MaxDate,
      ALL('DIM Date'))

Thanks

[v-jingzhang]

Hi @PoweredOut 

Not sure if I understand your requirement correctly. You may try this measure. Since there is no active relationship between two tables, you can use a slicer or filter to filter the date range and compare the rangeStart/rangeEnd dates with HireDate/TermDate directly. 

Test =
VAR rangeStart = MIN ( 'Dim Date'[Date] )
VAR rangeEnd = MAX ( 'Dim Date'[Date] )
RETURN
    CALCULATE (
        [Employee],
        'DIM Employee'[HireDate] <= rangeStart
            && 'DIM Employee'[TermDate] >= rangeEnd
    )

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!

[PoweredOut]

I have got the result I wanted with the below calculation, but it performs quite badly. 

Test = 
VAR TermDate = CALCULATE(MAX('DIM Employee'[TermHelp]), ALL('DIM Employee'),
        USERELATIONSHIP('DIM Employee'[TermHelp], 'DIM Date'[Date]))

VAR SumTotal =
IF(SELECTEDVALUE('DIM Date'[Date]) <= TermDate,

CALCULATE([Employees],
FILTER(
    ALL('DIM Date'),
        ISONORAFTER('DIM Date'[Date], MAX('DIM Date'[Date]), DESC)),
        USERELATIONSHIP('DIM Employee'[StartDate], 'DIM Date'[Date])))

VAR TermTotal = 
IF(SELECTEDVALUE('DIM Date'[Date]) <= TermDate,

CALCULATE([Employees],
FILTER(
    ALL('DIM Date'),
        ISONORAFTER('DIM Date'[Date], MAX('DIM Date'[Date]), DESC)),
        USERELATIONSHIP('DIM Employee'[TermHelp], 'DIM Date'[Date])))

RETURN
SumTotal - TermTotal

Any performance improvment suggestions would be greatly apprciated. 

[PoweredOut · Accepted solution]

I have a cleaner solution for my question. Loading time is quicker too

Employee Running Total = 
VAR StartDate =
    CALCULATE (
        'Employee Measures'[Employees],
        USERELATIONSHIP ( 'DIM Employee'[StartDate], 'DIM Date'[Date] ),
        FILTER ( ALL ( 'DIM Date' ), 'DIM Date'[Date] <= MAX ( 'DIM Date'[Date] ) )
    )
VAR Terms =
    CALCULATE (
        'Employee Measures'[Employees],
        USERELATIONSHIP ( 'DIM Employee'[TermHelp], 'DIM Date'[Date] ),
        FILTER (
            ALL ( 'DIM Date'[Date] ),
            'DIM Date'[Date] <= MAX ( 'DIM Date'[Date] )
        ),
        'DIM Employee'[TermHelp]
    )
RETURN
    IF (StartDate - Terms = 0, Blank(), StartDate - Terms)