YTD all employees

[hnguyen76]

Good afternoon,

I have a formula to retrieving the count of employees entering the company for YTD. The formula works in most instances with the exception of a month when there's no type of employees (trans in, new hire, contractor).  How can I retrieve and show the last available count within that month even if the type of employee is blank?

The formula is as followed:

04_YTD_IN = 
Var MaxMonth = IF(SELECTEDVALUE('Date'[Date].[Month]) <> BLANK(), SELECTEDVALUE('Date'[Date].[MonthNo]), MAX('Date'[Date].[MonthNo]))
var MaxYear = FORMAT(MAX('Date'[Date]), "YYYY")
var c_Formula = CALCULATE(SUM(HC[Core HC]), HC[EMP_IN] <> BLANK(), ALL('Date'), HC[YearNum] = MaxYear, VALUE(HC[MonthNum]) <= MaxMonth)
return
c_Formula

I have a date table with a relationship to my fact table called HC that counts the number of employees coming in from column EMP_IN. 

In the image below, the count is 49 for April but I'm only seeing a total of 42.

In the next image below, the count is 69 for May and it's populated correctly when all the fields are present.

So, the question is, how do I get 7 New Contractors from March to appear in April when it's blank? Thanks for the help!!!

[Anonymous]

Hi @hnguyen76

Can you please share some data or pbix file to explore a solution.

Cheers

CheenuSing

[hnguyen76 · Accepted solution]

Sorry for the late reply,

Realizing that the default YTD and custom YTD does not return all cumulative data within the date range I learned that switching over to DATESBETWEEN and ALL did the trick. For those wondering on the solution I came up with, here it is:

04_YTD_IN = 
var MaxYear = MAX(HC[YearNum])
var end_Date = DATEVALUE(SELECTEDVALUE('Date'[Date].[MonthNo]) & "/1/" & MAX('Date'[Date].[Year]))
var MinDate = CALCULATE(FIRSTDATE('Date'[Date]), ALL('Date'), HC[YearNum] = MaxYear)
return
CALCULATE(SUM(HC[Core HC]), FILTER(ALL(HC[EMP_IN]), HC[EMP_IN] <> BLANK()), ALL('Date'), DATESBETWEEN('Date'[Date], MinDate, end_Date))