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

[Anonymous]

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!

[OwenAuger · Accepted solution]

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

[Anonymous]

Thank you!!! Owen, 

It nicely worked. 

You inspired me with a good answer.  

Regards, 

Nash