Help to show roles that are ending

[MitchTrott]

Hi All,

I need to create a visualisation to show roles that are due to finish each month on a project.  I have the end dates in a cloumn named [Role Finish Date] along with other information such as [Name] [Project] [Role] [Function] etc so i should be able to create a graph to show how many roles are ending each month but for some reason it is getting the better of me.

This is what i have tried so far but it is returning some funky visuals that are way off:

Count of ending roles = 

VAR currentDate =

       MAX('Date Table'[Date])

RETURN

       CALCULATE(

              COUNTROWS(TTracker),

              FILTER(

                    TTracker,

                    (TTracker[Role Start Date <= currentDate && TTracker[Role Finish Date] >= currentDate)   

             )

       )

Any advice on creating this visual would be gratly appreciated

[johnt75]

Create a relationship from the date table to the role finish date, make it inactive if necessary, and then you can use

Num ending rows =
CALCULATE (
    COUNTROWS ( 'TTracker' ),
    USERELATIONSHIP ( 'Date'[Date], 'TTracker'[Role finish date] )
)

If you can make the relationship active then you would only need COUNTROWS('TTracker')