topic YTD values to Monthly - based on date and employee ID in DAX Commands and Tips

YTD values to Monthly - based on date and employee ID

JonathanGiord — Tue, 10 Oct 2023 09:29:07 GMT

Hi all,

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

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

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

some_bih — Wed, 11 Oct 2023 11:35:11 GMT

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!