Sum values based on a date range withing a date range

[Anonymous]

Hi 

I have an employee table with their working pensum like

Employee	From	To	Pensum
Aileen	2019/01/01	2019/01/31	100%
Aileen	2019/02/01	2019/12/31	80%
Barbara	2019/01/01	2019/12/31	60%
John	2019/06/06	2019/10/31	100%

In another table I manage the working periods (weeks per month)

Period	From	To
Period 01	2019/01/01	2019/01/06
Period 02	2019/01/07	2019/01/13
Period 03	2019/01/14	2019/01/20

Now I want to add to the second table a measure which sums the working pensum per period over all employees or if only one is selected kind of "lookup" his pensum for the period.

Thanks for your help

zemi

[Anonymous]

Can you see if this is what you had in mind?

Total Pensum = sum( Employee[Pensum] )


Measure = 
CALCULATE( 
    [Total Pensum], 
          FILTER( 
            Employee ,
            Employee[Start]>= MAX(WorkingPeriods[Start])
            && Employee[End] >= MAX(WorkingPeriods[Start])
        )
    )

in my measure, Start = From and End = To

[Anonymous]

Hi @Anonymous 

No, but maybe close.

Aileen works 100% in period 1 and Barbara works 60%. So it should sum up to 160%.

Feb 1st onward it wouldbe 80% + 60%

June 6th = 80 + 60 + 100

I ended up with (

Measure =

CALCULATE (

SUM ( Pensum[Arbeitspensum] );

FILTER (

ALL ( Pensum );

SELECTEDVALUE ( PeriodenKalender[Von] ) >= Pensum[Von] && SELECTEDVALUE ( PeriodenKalender[Bis] ) <= Pensum[Bis] &&

SELECTEDVALUE ( PeriodenKalender[Bis] ) >= Pensum[Von] && SELECTEDVALUE ( PeriodenKalender[Bis] ) <= Pensum[Bis]

))

But that just sums up any value 😞

Thanks anyway, zemi

[Anonymous]

I see. So the Weeks per month table has many more rows then?  

[Anonymous]

Hi @Anonymous 

Yes, the table goes down till the end of the year. Also employees we have many more 🙂 Sorry for not mentioning this.

zemi

[Anonymous]

@Anonymous ,

No problem at all, just wanted to sure I wasn't missing anything 🙂

I will try to work on this today for you

[Anonymous]

@Anonymous 

I think this will be closer to what you had in mind:

There's a few steps to get that point, so we'll start in Power Query first:

1. I created a DimEmployee Table with an ID for each employee. And then Merged with the actual employee table to get the ID in that table and then removed the employee name.  Proabably not needed, but was driving me crazy 🙂
2. Extended out the WorkingPeriods table
3. Then I added a column that will give me a list of all the dates in each period. This is more or less your date table, so I wanted to treat it as such

List.Dates(
[Start],
Duration.Days( [End]-[Start]) +1,
#duration(1,0,0,0)
)

1. Then just expand that out, remove the Start and End date columns since we no longer need them and you are all set to load them.
2. Related Working Periods to Employee and DimEmployee to Employee tables

Put Period Label on rows from working period and then measure:

Sum Based on Range = 
IF( 
    NOT( 
       ISBLANK( [Total Pensum] )
    ),
    SUMX( VALUES( DimEmployee),
        CALCULATE(
            [Total Pensum],
            CALCULATETABLE(
                Employee,
                FILTER( 
                    ALL(WorkingPeriods),
                    CALCULATE( MIN( Employee[Start]))  <=   MAX( WorkingPeriods[Dates])
                    &&
                    CALCULATE( max( Employee[END]))  >=   MAX( WorkingPeriods[Dates])
                )
            )
        )
    )
)

Here is the PBIX:

https://1drv.ms/u/s!Amqd8ArUSwDS0Ftkhm57vGRV_6K1

Let me know what you think!

---

---

[Anonymous · Accepted solution]

Hi @Anonymous 

Many thanks for input and ideas. Actually it does very close to what I need and was of great help.

But I found a smarter and also nicer way. This code actually works (in German but I guess you can read it):

Arbeitspensum = 
CALCULATE (
    SUM ( Pensum[Arbeitspensum] );
    FILTER (
        ALL ( Pensum[Von]; Pensum[Bis] );
        SELECTEDVALUE ( PeriodenKalender[Von] ) >= Pensum[Von] && SELECTEDVALUE ( PeriodenKalender[Bis] ) <= Pensum[Bis] && 
        SELECTEDVALUE ( PeriodenKalender[Bis] ) >= Pensum[Von] && SELECTEDVALUE ( PeriodenKalender[Bis] ) <= Pensum[Bis]
    )
)

It adds the following red data 

Period	From	To	WorkPensum if all are selected	WorkPensum for Aileen only
Period 01	2019/01/01	2019/01/06	160%	100%
Period 02	2019/01/07	2019/01/13	160%	100%
Period 03	2019/01/14	2019/01/20	160%	100%
...			...	...
Period 06	2019/02/01	...	140%	80%
...			...	...
Period 27	2019/06/06	...	240%	80%
...			...	...

Thanks again, zemi