Forum Discussion
yaman123
Post Partisan
Post PartisanPrevious Month Headcount
Hi all, I am trying to calculate previous months headcount. I have the below to calculate the headcount per month Headcount = var selectedDate = MAX('Date'[Date]) RETURN CALCULATE(DISTIN...
Hi, yaman123
According to your description, if you have month and year column in date table, you can try like this:
Headcount = VAR year = SELECTEDVALUE ( 'Date'[year] ) VAR month = SELECTEDVALUE ( 'Date'[month] ) VAR date = DATE ( year, month, 1 ) VAR previousfirstday = EDATE ( date, -1 ) VAR previouslastday = EOMONTH ( date, -1 ) RETURN CALCULATE ( DISTINCTCOUNT ( 'Headcount by Function'[EMP_NO] ), 'Headcount by Function'[VALID_FROM] >= previousfirstday || ISBLANK ( 'Headcount by Function'[VALID_TO] ), 'Headcount by Function'[VALID_TO] <= previouslastday )If it doesn’t solve your problem, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-janeyg-msft
Community Support
Community SupportHi, yaman123
According to your description, if you have month and year column in date table, you can try like this:
Headcount =
VAR year =
SELECTEDVALUE ( 'Date'[year] )
VAR month =
SELECTEDVALUE ( 'Date'[month] )
VAR date =
DATE ( year, month, 1 )
VAR previousfirstday =
EDATE ( date, -1 )
VAR previouslastday =
EOMONTH ( date, -1 )
RETURN
CALCULATE (
DISTINCTCOUNT ( 'Headcount by Function'[EMP_NO] ),
'Headcount by Function'[VALID_FROM] >= previousfirstday
|| ISBLANK ( 'Headcount by Function'[VALID_TO] ),
'Headcount by Function'[VALID_TO] <= previouslastday
)If it doesn’t solve your problem, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.