Headcount measure using disassociated calendar table

[Newbie777]

Hello,

I am trying to create headcount measure using the employee table and with the information of hiring date and leaving date and the calendar table which are disassociated with each other to enable dynamic calculation based on the different points in time dimension.  I used the following measure hoping to see the HC to be calculated flexibly with respect to different points in time dimension, but the outcome of the calculation is zero.  I would appreciate it if anyone can point me in the right direction where I am going wrong with this measure.  

Thanks a lot for your help.

[Kushal_Chawda]

@Newbie777  try below

=if( max(Total MonthYear) >= [Hiring date] and max(Total MonthYear) <= [Leaving date], count(..))

[Newbie777]

Thanks for your response, but I am getting the zero as the answer.  I guess it could be due to the fact that there is no MonthYear dimension in my data model.  I tweaked it a bit, and tried 

if( max(Total [Month Year])>=Alt(date([Hiring date]), today(0)) and max(Total [Month Year])<=Alt(date([Leave date]), today(0)),count([First name last name]),0)

but it doesn't produce non-zero answer either.  I'd greatly appreciate it if you could let me know what I am doing wrongly.  

Thanks a lot!

[Kushal_Chawda]

@Newbie777  Do you have sample data? Without looking at your data it will be difficult to tell what is wrong

[Newbie777]

Thanks for your response, but it is not easy to share the data as I need to replace the content with fake data. 

The expression below is producing non-zero answer, but it just seems to be count of all the people on the database without reference to time dimension.  

if( today(0)>=Alt(date([Hiring date]), today(0)) and today(0)<=Alt(date([Leave date]), today(0)),count([First name last name]),0)

[Stinnett14]

The calendar table is a table I created to create a relationship between the 3 tables based on dates.

Omegle

[Newbie777]

Thanks, but the way I thought this would work is not by creating a relationship between calendar table and employee database.  If I created relationship with calendar table with either hiring date and leaving date it will not work. So I specifically disassociated the tables, like I would do in PowerPivot model.  I am not familiar with Qlik Sense, but I would imagine that it will work in a similar way with Power BI in terms of headcount calculation.   

[Newbie777]

I couldn't find the reason after spending several hours on this issue.  I'd greatly appreciate it if you could let me know why the measure expression below is not working, and just producing 0 as the calculated result for all the time periods.  I attach the sample qvf file.  I'd greatly appreciate it if you could check the master measure I created named "Headcount".  I've kept calendar table and employee table disassociated.  

[Newbie777]

Here is the qvf file with the sample data.  It contains 4 tables but only two are relevant for this measure.  I tried to delete the two other irrelevant tables, but I couldn't, sorry. 

[Newbie777]

Hello Kushal_Chawda,

I'd greatly appreciate it if you could have a look at the qvf file and let me know why I am getting zero as a calculation result.  Thank you in advance for your time.