total ytd of active employee in month end having start and end date

[Minu_Latha]

Have a table EmpActivity with Columns Employee Number,Start Date,End Date.Created a disconnected table CalenderAuto.

It is used as Slicer for Year.Need to get the Active employee YTD count on each month end of selected year.If the employee is currently working  End Date is Blanl

My output must be like 

eg.2017 is selected

Month	Count	Ytd
Jan	10	10
Feb	8	18
Mar	10	28

Please help.I am new to power BI

[v-alq-msft · Accepted solution]

Hi, @Minu_Latha 

Based on your description, you may create a calculated column and measures as below. The pbix file is attached in the end.

Calculated column:

YearMonth = YEAR([Date])*100+MONTH([Date])

Measure:

EndOfMonth = MAX('Calendar'[Date])

ECount = 
var startdate = MIN('Calendar'[Date])
var enddate = MAX('Calendar'[Date])
var res = 
    CALCULATE(
        DISTINCTCOUNT('BI EmpActivity'[BI.Personnel.EmpNo1]),
        FILTER(
            ALLSELECTED('BI EmpActivity'),
            NOT(
                OR(
                    [FromDate]>enddate,
                    [ToDate]<startdate
                )
            )
        )
    )
return
res

YTD = 
var t = ALLSELECTED('BI EmpActivity')
var t1 = 
SUMMARIZE(
    ALLSELECTED('Calendar'),
    'Calendar'[YearMonth],
    "Result",
    var startdate = MIN('Calendar'[Date])
    var enddate = MAX('Calendar'[Date])
    var res = 
    CALCULATE(
        DISTINCTCOUNT('BI EmpActivity'[BI.Personnel.EmpNo1]),
        FILTER(
            t,
            NOT(
                OR(
                    [FromDate]>enddate,
                    [ToDate]<startdate
                )
            )
        )
    )
    return
    res
)
return
SUMX(
    FILTER(
        t1,
        [YearMonth]<=MAX('Calendar'[YearMonth])&&
        LEFT([YearMonth],4)=LEFT(MAX('Calendar'[YearMonth]),4)
    ),
    [Result]
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Minu_Latha]

I need to know something to complete my work on YTD.
I have another table EmpInactivehistory with FromDate and this date have relation with calendar(date) Created.
I already have EmpActivity table which has no relationship with Calendar(date). Count & YTD is calculated from this Table based on count of employees at each month end (already done)
But the existing relationship causes the dates to be filtered out.
For the year 2019
EmpInactiveHistory have only 1 record in June
But year 2019
Empactivity based Count exists for all months.But Showing only 1 month(June) YTD.

RelationShip

When Relationship between Calendar(date &EmpInactiveHistory(FromDate) Removed output  from Empactivity is correct

Formula for Employee Count from EmpActivity based on month end

[Minu_Latha]

It works fine Many Thanks

[PC2790]

Connect Calendar Table with your main Fact table which contains all the data by creating a relationship. This will enable you to perform all the calculations involving dates.

Then to view a table with the months and the number of candidates, select the table visual and add Month from Calendar Table and a newly created measure [NoOfEmployees] which would look something like:

NoofActiveEmployees = CALCULATE(COUNTROWS(TableName),filter(TableName,TableName[EndDate] <> BLANK())

Try and see if it gives you the desired result

[Minu_Latha]

My EmpActivity table have Start & End Date.So relationship cant be established

Emp No     Start Date        End Date       Activity

1                 05-02-2020    20-03-2020     Leave

2                 01-01-2020                            Still working

So when Slicer Year Selected 2020

Month     Empcount     YtdCount

Jan            1                    1

Feb            2                    3

Mar            1                   4

Need to get output like this .What to do

[PC2790]

Can you provide your code for the Date Table?

[Minu_Latha]

More Details

[PC2790]

Hello @Minu_Latha 

Please check if this fulfills your requirement?

https://www.dropbox.com/s/18mq6f9jradjqxg/Sample1_updated.pbix?dl=0

[Minu_Latha]

Sorry Its not the desired output.Non blank records cannot be considered as active.The employee is active till now thats the meaning of End Date is null

[PC2790]

Yes, you are right.

Modify the measure as below:

NoofActiveEmployees = CALCULATE(COUNTROWS('BI EmpActivity'),filter('BI EmpActivity','BI EmpActivity'[ToDate] = BLANK()))

To verify this thing, you can create a calcultaed column using the code:

Active/Inactive = If('BI EmpActivity'[ToDate] = Blank(), "Active","Inactive") 

[Arul]

Hi @Minu_Latha ,

Could you please explain it in more datailed manner? So that it would be helpful for me to understand and solve.

Thanks,

-Arul

[Minu_Latha]

EmpActivity table contains Employee Details

Employee No

Start Date

End Date

Created Table Calendar using Calendarauto having no relation with EmpActivity

Year from Calendar used as slicer.Get the employee count in each month end & its ytd in each month