topic Re: Calculate how many hours a worker gained as "overtime" between 01.2022 and 12.2022 in DAX Commands and Tips

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

Laura1996 — Wed, 20 Dec 2023 10:07:53 GMT

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

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

Greg_Deckler — Wed, 20 Dec 2023 11:43:43 GMT

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

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

Anonymous — Fri, 22 Dec 2023 05:47:34 GMT

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

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

Laura1996 — Mon, 08 Jan 2024 07:31:01 GMT

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 😉