YTD values to Monthly - based on date and employee ID

[JonathanGiord]

Hi all,

I have the following table named "Absences", here is a simplified version of it:

employee ID	Key date (EOM)	Sick days (YTD)
1	28. Feb	20
2	28. Feb	3
3	28. Feb	2
1	31. Mrz	25
2	31. Mrz	6
3	31. Mrz	4

I would like to create a DAX formula to obtain the Monthly value as stated below in my table: 

employee ID	Key date (EOM)	Sick days (YTD)	expected column Monthly
1	28. Feb	20	20
2	28. Feb	3	3
3	28. Feb	2	2
1	31. Mrz	25	5
2	31. Mrz	6	3
3	31. Mrz	4	2

Please note that my data only starts as of February 2023 (no data for Jan 2023), thanks a lot for your help! 

[some_bih · Accepted solution]

Hi @JonathanGiord possible solution, create calculated column; adjust your table name for  Sheet1

YTD SD =

VAR __current_employee = Sheet1[employee ID]

VAR __date_eom = Sheet1[Key date (EOM)]

VAR __previous_row_sick_days =

    CALCULATE (

        SUM ( Sheet1[Sick days (YTD)] ),

        FILTER (

            Sheet1,

            Sheet1[employee ID] = __current_employee

                && Sheet1[Key date (EOM)] < __date_eom

        )

    )

VAR __Result = Sheet1[Sick days (YTD)] - __previous_row_sick_days

RETURN

    __Result

Output

Did I answer your question? Kudos appreciated / accept solution!