Power Bi - Require solution for the question

[jeevar]

I have an excel file which has the list of employees and their Date of Joining. I also created the calendar table and completed the relationship.

I need to get the count of employees to be calculated as follows:

1. I need the count of employees for each month YTD (I used TOTALYTD and it works fine)
2. I need the count of employees to be carried forward after each Fiscal Year - For example, the count as on 31-December-2016 should be taken the opening balance for 01-January-2017.

Can anyone help in this regard?

​

 

[Matt Allington]

Do they ever leave? Normally I would expect a start date and a departure date. There are various approaches. You can either not create a relationship and apply a virtual relationship, or you could write a "lifetime to date" measure using the physical relationship. Have a read of my blog article here DAX Time Intelligence Explained - Excelerator BI


employees = calculate(countrows(employeeTable),filter(employeeTable,employeeTable[date] <= max(calendar[date])))

 

[jeevar]

Thanks Matt. That was indeed very useful