topic Re: How to calculate YTD values of only for active employees at that time. in DAX Commands and Tips

How to calculate YTD values of only for active employees at that time.

Anonymous — Tue, 04 Jan 2022 01:49:18 GMT

Hi, Happy New year!

I want to get a solution for my model.

I assumed that the solution I want is about Semi-additive calculations. but it was different.

(I referenced this article from "daxpatterns.com")

So, If you can help me, I really appreciate that.

Here is a description of the question.

* Purpose: Usage status management of vacations
* Desired Result (Measure): YTD Number of used vacations at each time point (only for active employees at that time)
- You can check the Desired result from the underneath table.

* My model is here

* Here is the result table. and two measures(Columns) are like this

- Sum_Vac : Sum( 'Fact_Vacation'[#ofDays] )
- YTD_Vac : Calculate ( 'Fact_Vacation'[#ofDays], DATESYTD ('DIM_Calendar'[Cal_Date]) )

I want to get a measure for "Desired Result".

The important point for measure("Desired Result") is
after employee resigned (for this example: Emp_ID is "A120"),
the values that the resigned employee had used until 11, Oct 2019 should be extracted from "Desired Result" in columns "H" and "K".
Therefore, From 12, Oct 2019, the outstanding values for the active employees (for this example: Emp_ID is "A115") should be remaining.

If you need detailed data about it, I surely give it to you.

(Actually, I could not find the way of attaching files)

If you need additional explanations or Sources about this, please feel free to contact me.

And any recommendations about articles related to this concept are always welcome.

Thank you!

Re: How to calculate YTD values of only for active employees at that time.

OwenAuger — Tue, 04 Jan 2022 02:45:52 GMT

Hi Anonymous

If I understand you correctly, you want to modify the existing YTD_Vac measure so that only Employees who have not resigned as at the currently filtered date are included.

You can do this by adding an additional filter such that Date_Resign is either blank or greater than the "currently filtered date".

We can choose how to define "currently filtered date", but I'm going to use the maximum visible date in the filter context.

With these assumptions, you could write a measure like this, making use of the existing YTD_Vac measure:

YTD_Vac for employees not yet resigned = VAR MaxDate = MAX ( Dim_Calendar[Cal_Date] ) RETURN CALCULATE ( [YTD_Vac], KEEPFILTERS ( OR ( ISBLANK ( Dim_EmpInfo[Date_Resign] ), Dim_EmpInfo[Date_Resign] > MaxDate ) ) )

You could also combine the logic into one measure (assuming can reference the Sum_Vac measure):

YTD_Vac for employees not yet resigned = VAR MaxDate = MAX ( Dim_Calendar[Cal_Date] ) RETURN CALCULATE ( [Sum_Vac], DATESYTD ( 'Dim_Calendar'[Cal_Date] ), KEEPFILTERS ( OR ( ISBLANK ( Dim_EmpInfo[Date_Resign] ), Dim_EmpInfo[Date_Resign] > MaxDate ) ) )

Notes on the above:

I suggest using KEEPFILTERS to ensure that we combine the Date_Resign filter with any existing filters on Date_Resign that might exist.
By using the condition that Date_Resign > MaxDate, we exclude any Employees who resigned before/during the currently filtered period. For example, if you displayed this measure by month rather than date, you would only include Employees whose Date_Resign is after the filtered month (or blank).

Hopefully the above is useful and can be adapted to your model, but please post back if needed 🙂

Regards,

Owen

Re: How to calculate YTD values of only for active employees at that time.

Anonymous — Tue, 04 Jan 2022 09:53:42 GMT

Thank you!!! Owen,

It nicely worked.

You inspired me with a good answer.

Regards,

Nash