calcualte head count of employee using dax calculation

[sunil222]

table has employee name,doj,lwd columns , calculate head count of employees when employee terminates particular month , before months shows 1 value , after termination shows 0 value  and termination month also shows 0 value  in power bi dax     , date table is also there 

requirement as below please help me on this ,    employee terminates on feb2 , before months shows 1 , feb month also 1 , next months onwards shows 0 

empname           doj                lwd                           dec 23      jan 24   feb 24   mar 24  apr 24  may  24

raju              18-03-2020         02-02-2024                  1                1          1            0           0          0

[Anonymous · Accepted solution]

Hi @sunil222 ,

Please follow these steps:

1.Using a dax expression, create a date table.

True =

CALENDAR(

    DATE(2023,11,1),

    DATE(2024,4,1))

2.Use a dax expression to create a calculated column for the year and month for the date table.

Month_year =

FORMAT('True'[Date],"mmm") &" "&RIGHT(YEAR('True'[Date]),2)

3.Create a measure to determine whether the date in the date table is less than the date of departure in the current table and assign a value to it.

Measure =

var _select=

SELECTCOLUMNS(

    FILTER(ALL('True'),

    'True'[Date]<=MAX('Table'[LWD])),"test",'True'[Month_year])

return

IF(

    MAX('True'[Month_year]) in _select,1,0)

4.At this time to create a matrix and in accordance with the following chart into the rows, columns, value fields, you will find that the date of the arrangement of the order is wrong, that is because the power bi default use of alphabetical sorting at this time.

5.Use the dax expression to create a calculation table again for sorting dates.

True 2 =

SUMMARIZE(

    'True','True'[Month_year],"min",MINX(FILTER(ALL('True'),'True'[Month_year]=EARLIER('True'[Month_year])),[Date]))

6.Select the Month_year column in the calculation table you just created and modify its sorting sequence.

7.Create a relationship for the two tables.

8.Change the column field in the matrix to the Month_year column in True 2. The final result is shown below.

If your Current Period does not refer to this, please clarify in a follow-up reply.

^{Best Regards,}

^{Clara Gong}

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

[sunil222]

Please help me on this

[Greg_Deckler]

@sunil222 Not sure I am completely following this but perhaps:

Measure =
  VAR __lwd = MAX('Table'[lwd])
  VAR __Date = MAX('Dates'[Date])
  VAR __Result = 
    SWITCH(TRUE(),
      __Date <= __lwd, 1,
      YEAR(__Date) = YEAR(__lwd) && MONTH(__Date) = MONTH(__lwd), 1,
      0
    )
RETURN
  __Result

Otherwise, Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[sunil222]

sample data 

EMP_Name	Designation	DOJ	LWD
Ruby Patel	Delivery manager	6/20/2021	1/15/2024

output:

Ruby patel has lwd so termination month and next month shows 0 before months shows values as 1

EMP_Name	Designation	DOJ	LWD	jan 24	Feb 24
			1/15/2024	1	0