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

     

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)