Count is different. I want to calculate headcount as per the date selected.

[Anonymous]

Dear Folk,

Please help me i am trying to calculate the headcount from the start date to the last date of the selected year and month. 

kindly check the below DAX function I have used. 

for Working count:-

Working_MTD = CALCULATE('Measure Organize'[empCount],'Employee Dump'[STATUS]="Working",DATESBETWEEN(Date_Table[Date],'Measure Organize'[minDate],'Measure Organize'[endDate]))

The above table is showing working MTD head count but in august which is the current month and the total is showing different.

[Anonymous]

Hi @Anonymous ,

It is suggested to create a seperate year and month table for the slicer.

You can create a yearmonth table like

YearMonth =
DISTINCT (
    SELECTCOLUMNS (
        'Date_Table',
        "Year", YEAR ( [Date] ),
        "Month", MONTH ( [Date] )
    )
)

Then create the Working_MTD measure like

Working_MTD = 
VAR _year =
    SELECTEDVALUE ( YearMonth[Year] )
VAR _month =
    SELECTEDVALUE ( YearMonth[Month] )
RETURN
    CALCULATE (
        [empCount],
        FILTER ( 'Employee Dump', [STATUS] = "Working" ),
        FILTER (
            'Date_Table',
            [Date] <= EOMONTH ( DATE ( _year, _month, 1 ), 0 )
                && [Date] >= MIN ( 'Date_Table'[Date] )
        )
    )

Best Regards,

ShundaSteph

[Anonymous]

Hi @Anonymous ,

It is suggested to create a seperate year and month table for the slicer.

[Anonymous]

I appreciate it if someone helps me.

[amitchandak]

@Anonymous , can share the logic for MinDate and Enddate

Also, check if these types of formulas can help

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

MTD =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max,-1)+1 ,
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

This Month =
var _max = eomonth(if(isfiltered('Date'),MAX( 'Date'[Date]) , today()),0)
var _min = eomonth(_max,-1)+1 ,
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

[Anonymous]

Please find below for minDate & endDate logic.

minDate = calculate(min(Date_Table[Date]),ALL(Date_Table[Year],Date_Table[Month_Name]))

endDate = max(Date_Table[Date])