topic Re: How to Get First Non Zero Value of current year in DAX Commands and Tips

How to Get First Non Zero Value of current year

Lexi_Liu — Mon, 15 Apr 2024 10:04:09 GMT

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!

Re: How to Get First Non Zero Value of current year

Joe_Barry — Mon, 15 Apr 2024 12:34:42 GMT

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

Re: How to Get First Non Zero Value of current year

Lexi_Liu — Tue, 16 Apr 2024 02:48:36 GMT

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!

Re: How to Get First Non Zero Value of current year

Joe_Barry — Tue, 16 Apr 2024 05:27:01 GMT

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

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

Re: How to Get First Non Zero Value of current year

Lexi_Liu — Tue, 16 Apr 2024 05:56:53 GMT

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.