Measure for Dynamic sum in variable interval

[hanswittoeck]

Hello

I need a total for a flexible time interval. Thos os the data model

1 table with dates, operator and worked hours

1 table indicating the start and end date, the operator and a som of the hours between start and stop

Operator	day	Worked			start	end	operator	sum Worked
OP1	2020-01-05	8			2020-01-06	2020-01-11	OP1	??
OP1	2020-01-06	7.5			2020-01-05	2020-01-11	OP2	??
OP1	2020-01-07	6
OP1	2020-01-08	8.5
OP1	2020-01-09	10
OP1	2020-01-10	7.5
OP1	2020-01-11	4
OP1	2020-01-12	8
OP2	2020-01-05	10
OP2	2020-01-06	7.5
OP2	2020-01-07	4
OP2	2020-01-08	8
OP2	2020-01-09	10
OP2	2020-01-10	7.5
OP2	2020-01-11	4
OP2	2020-01-12	8

Any idea how this can be done?

Help is highly appriciated

Hans 

[hanswittoeck]

sorry for the others, but the first solution worked for me, so no need to try anythinh else that can be as valuable

Hans 

[Greg_Deckler]

Perhaps:

Table =
  GROUPBY(
    'Table',
    [Operator],
    "start",MINX(CURRENTGROUP(),[day]),
    "end",MAXX(CURRENTGROUP(),[day]),
    "sum",SUMX(CURRENTGROUP(),[Worked])
  )

@hanswittoeck , I'm with @AlB I wasn't sure if you wanted a DAX table, a table visualization or what exactly.

[AlB · Accepted solution]

Hi @hanswittoeck 

You need a measure or a calculated column? If the second table is an actual table (rather than a visual), you can create a calculated column for Table2:

Calc_column =
CALCULATE (
    SUM ( Table1[Worked] ),
    FILTER (
        ALL ( Table1 ),
        Table1[day] >= Table2[Start] && Table1[day] <= Table2[End]
            && Table1[operator] >= Table2[operator]
    )
)

 Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers 

[hanswittoeck]

Yes,

this one works, except for one glitch:

Table1[operator] >= Table2[operator]

should be '=' not '>='

But I'm sure you were only checking my perceptiveness 😉 

Thanks a lot for your help

Hans