Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:-
The above table is showing working MTD head count but in august which is the current month and the total is showing different.
Hi @gauravtomar ,
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
I appreciate it if someone helps me.
@gauravtomar , 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))
Please find below for minDate & endDate logic.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
80 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |