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)

     

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!