Glide path using from to date in data

[easton16]

Good Evening All,

A simple one I am hoping but going around in Circles at present.

I am making a glide path to show total number of active items each period (have date look up table for period due to changing each year). Each item has a start date and removal date. What is the simplest way of doing this?

Currently have two relationships set up between date in periods dates table and Planned Removal Date & Speed Imposed date and being trying to use a measure with calculate function.

Any help much appreciated

[Anonymous · Accepted solution]

Hi @easton16 ,

You can refer the content in the following links to get it.

Measure to count active employees in a period based on Slicer Calendar

Count of Active Users In date range

Calculate OPEN CASES over time in Power BI

1. Have a date dimension table first

2. Create a measure as below

Employee Count = 
VAR __DATE = MAX ( 'Date'[Date] )
RETURN
    SUMX (
        EmployeeTable,
        IF (
            EmployeeTable[DateStarted] <= __DATE
                && OR ( EmployeeTable[Leavedates] >= __DATE , ISBLANK(EmployeeTable[Leavedates]) ),
            1,
            BLANK ()
        )
    )

3. Create visual

Best Regards

[easton16]

Thanks @amitchandak , Is there anyway I could tweak it slightly?

The finish date in my case is planned in the future or blank if not yet planned to be removed? I have tried to remove the 'ISBLANKS(' bit and some other tweaks but struggling to make it count all of the ones that would fall between the start and planned  finish date or count if blank?

Many Thanks

[amitchandak]

@easton16 , This blog and the attached file should help. Current employee you are looking for

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...