How to Get First Non Zero Value of current year

[Lexi_Liu]

Is there a way to get the first non zero value of current year? I tried Firstnonblank but it only returns selected month data.

My Fact Table 'Employee' contains [Effective Date] and [employee ID], each row stands for an employee. The effective dates are end day of each month.

I have a Date slicer, contains each month end.

[Employee of Current Month] = CALCULATE(TOTALMTD(countrows('Employee'),'Date'[Date]))

[Last Year End Employee] = CALCULATE([Employee of Current Month], ENDOFYEAR(PREVIOUSYEAR('Date'[Date])))

Now I want to use DAX to create a condition, if the [Last Year End Employee]= 0, then return the first month's value that the number of employee is greater than 0.

Appreciate for your help!

[Joe_Barry]

Hi @Lexi_Liu 

Does the Employee only appear once in the table or is there an entry for each month for the Employee? Can you explain in more detail in what you want to exactly acheive? Are you trying to get current employee amount and last years amount? Is there a termination date also? If you can, please provide a sample of the table

Thanks

Joe

[Lexi_Liu]

Hi Joe,

Thank you for your reply, here is the sample table. There is no termination date, each row stand for an active employee as of month end. The effective date column has relationship with DimDate table.

I use [Last Year End Employee] to get the employee amount as of last year end, however there was no Japan employee as of 12/31/2023, so I want a DAX to show the next month data when the amount is greater than 0, which is 2, as of 1/31/2024.

When user select 2/29/2024 as the effective date, or any future date this year, ideally the [Begin Amount] for Japan should always be 2 (as of 1/31/2024).

I tried [Begin Amount] = if([Last Year End Employee]=0, (FIRSTNONBLANKVALUE('DimDate'[Date], [Employee of Current Month])),[Last Year End Employee]) but it returns 3 for Japan employee amount, that is as of 2/29/2024 (the selected effective date).

Thanks!

[Joe_Barry]

Hi @Lexi_Liu 

Maybe you are overthinking here?

Create a measure as base measure to count Employees 

Employee = DISTINCTCOUNT(Employee[Employee ID])

 When you add this alone to a Matrix, this will show all the month/years where there is an employee active.

You can also create measures to calculate This year's and last year's numbers

Employee YTD = TOTALYTD([Employee], 'DIM Date Test'[Date])

YTD 

PY 

Employee PY = 
CALCULATE ( [Employee], DATEADD ( 'DIM Date Test'[Date], -1, YEAR ) )

Hope this helps

Joe

If you found my answer helpful and it solved your issue, please accept as solution

[Lexi_Liu]

Hi Joe,

Thanks for your detailed solution.

The reason we need Begin amount is because when calculating the average amount, the ending amount is as of the selected month end, and the begin amount, usually it's the last year end amount, unless it's 0, then we should use the first non-zero amount as begin amount.

So I'm thinking if there is a way to achieve this.