Total Headcount progression on current year

[arcall]

Hello All,

I am trying to achieve a total headcount visual for the following.

I have a table with the following columns.

Name of emp, hire date, termination date, Active(y or n), Class (Production or Support/...)

The hire date started many years ago.

I want to create the following visual that will show the overall headcount by month, but only show the current year up to the current month. Here is what i want to reproduce.

Your help is appreciated.

Thanks

[v-lili6-msft]

hi, @arcall 

Here is a similar post for you refer to:

https://community.powerbi.com/t5/Desktop/Measuring-logged-workdays/m-p/613517#M292418

For your requirement, you just need to do some adjustment:

Step1:

For date table, add a tag for months up today of the current year in the complete date table.

For example:

tag = IF(YEAR('Date'[Date])=YEAR(TODAY())&&MONTH('Date'[Date])<=MONTH(TODAY()),"y","n")

Step2:

When create a crossjoin table, add this conditional "Date "[tag]="y" and Active="y"

Step3:

Then create a Total Headcount measure and then drag date and Class (from new table) in visual

Total Headcount= CALCULATE(COUNTA('Table'[Name of emp]))

Best Regards,

Lin

[arcall]

Hi Lin,

I am not getting the visual I am expecting. 

First, I just want to clarify about the date tag. I have a date table with a relationship to the databable (Called "HR Data"). I presume the date tag is under that date table and is a column ? Is the relationship between the date and HR Data will confuse this measure ?

Here is the DAX command for tag.

tag = IF(YEAR('Date'[Date])=YEAR(TODAY())&&MONTH('Date'[Date])<=MONTH(TODAY()),"y","n")

In the page date filter, do I select all years ?

Here is my crossjoin table :

Table = FILTER(CROSSJOIN('Date','HR Data'),'Date'[tag]="y"&&'HR Data'[Active]="Yes"&&'Date'[Date]>='HR Data'[Hire Date]&&'Date'[Date]<='HR Data'[Termination/Release Date])

I have a graphic visual with "Shared Axis" with Month from Date table amd Total HeadCount from the following measure.

Total Headcount = CALCULATE(COUNTA('HR Data'[Name]))

Here is what i've got. 

I should have a cumulative amount and it should stop at April.

What am I doing wrong ?

Thanks

[v-lili6-msft]

hi, @arcall 

For the date tag, it works well on my side.

There should be something else wrong, could you please share a simple sample pbix file and expected output. for us have a test.

Best Regards,
Lin

[arcall]

I also used this in my measure 

Total Headcount = CALCULATE(COUNTA('table'[Name])). doesn't work.