Calculate how many hours a worker gained as "overtime" between 01.2022 and 12.2022

[Laura1996]

Hey,

I have the following problem. I have two tables:

Salden:

Personalnr.	Name	Date	GLZ
18984	Laura	01.01.2022	65,68
18984	Laura	31.12.2022	37,75

and the table

Leavings:

PersonalNr.	Name	Days on "leave" (GLZ)
18984	Laura	123,5

What I would like to do is:

65,68 (=january value) - 123,5 = -57,42 - 37,75 (=december value) = 94,99 (this is the number that should be displayed in the end. Hopefully someone can help me.

this was my first try:

Spalte = IF(Salden[Stichtag].[Date]="01.01.2022",SUM(Salden[GLZ])-RELATED(Days on "leave" (GLZ)])-IF(Salden[Stichtag].[Date]="31.12.2022",Salden[GLZ]))

but I need this per "person" so I will need to use also the personalNr.? Because every Person has a december and january date.

Thank you in advance

[v-yangliu-msft · Accepted solution]

Hi  @Laura1996 ,

Here are the steps you can follow：

1. Create calculated column.

Column =
var _januarey=
SUMX(
    FILTER(ALL(Salden),'Salden'[Name]=EARLIER('Salden'[Name])&&'Salden'[Date]=DATE(2022,1,1)),[GLZ])
var _december=
SUMX( FILTER(ALL(Salden),'Salden'[Name]=EARLIER('Salden'[Name])&&'Salden'[Date]=DATE(2022,12,31)),[GLZ])
var _value=
SUMX(
    FILTER(ALL(Leavings),
  'Leavings'[Name]=EARLIER('Salden'[Name])),'Leavings'[Days on "leave" (GLZ)])
return
IF(
    'Salden'[Date]=DATE(2022,12,31),
_januarey-_value-_december,BLANK())

2. Result:

Additionally, in calculating the data you provided, I noticed a small discrepancy that I would like to share with you. About 65.68-123.5-37.75=-95.57.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Laura1996]

Thank you so  much 🙂 the solution works like a charm.

About the discrepancy: I wrapped the formula into "

ABS(_januarey-_value-_december)" - now the minus is gone 😉

[Greg_Deckler]

@Laura1996 Not quite sure I am following but you could put PersonalNr from your first table into a table visual and have a measure like this:

Spalte Measure =
  VAR __January = SUMX( FILTER( 'Salden', [Date] = "01.01.2022", [GLZ])
  VAR __December = SUMX( FILTER( 'Salden', [Date] = "31.12.2022", [GLZ])
  VAR __Leave = SUMX( 'Leavings', [Days on "leave" (GLZ)] )
  VAR __Result = __January - __Leave - __December
RETURN
  __Result