Forum Discussion

PoweredOut's avatar
PoweredOut
Resolver I
3 years ago
Solved

RunningTotal with Userelationship

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

 

 

Need Help
  • PoweredOut's avatar
    PoweredOut
    3 years ago

    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)

     

3 Replies

  • PoweredOut's avatar
    PoweredOut
    Resolver I
    3 years ago

    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's avatar
      PoweredOut
      Resolver I
      3 years ago

      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)

       

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    3 years ago

    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!