Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Sum values based on a date range withing a date range

Hi 

 

I have an employee table with their working pensum like

EmployeeFromToPensum
Aileen2019/01/012019/01/31100%
Aileen2019/02/012019/12/3180%
Barbara2019/01/012019/12/3160%
John2019/06/062019/10/31100%

 

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

PeriodFromTo
Period 012019/01/012019/01/06
Period 022019/01/072019/01/13
Period 032019/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

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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 
    PeriodFromToWorkPensum if all are selected WorkPensum for Aileen only
    Period 012019/01/012019/01/06160%100%
    Period 022019/01/072019/01/13160%100%
    Period 032019/01/14

    2019/01/20

    160%

    100%

    ... 

     

    ...

    ...

    Period 062019/02/01

    ...

    140%

    80%

    ... 

     

    ...

    ...

    Period 272019/06/06

    ...

    240%

    80%

    ... 

     

    ...

    ...

     
    Thanks again, zemi

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

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