cancel
Showing results for
Search instead for
Did you mean:
Resolver II

## YTD all employees

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!!!

1 ACCEPTED SOLUTION
Resolver II

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))
2 REPLIES 2
Community Champion

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

Cheers

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Resolver II

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))

## Helpful resources

Announcements

#### Check it Out!

Click here to learn more about the August 2022 updates!

#### The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

#### Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors