Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculate Sum in Period Where Periods Differ By Project

Hello,

 

I am new to Power Query/Power BI. I have the below tables -

 

Ticket Data table

TicketStartFinishProjectSize
JOK-4575617/12/201917/12/2019JOK3
JOK-69315611/11/201917/12/2019JOK3
JOK-63815617/11/20199/12/2019JOK3
JOK-4811563/12/20199/12/2019JOK2
PJMFV-1001/12/2019 PJMFV5
PJMFV-15425/11/201916/12/2019PJMFV1
PJMFV-5711/11/20193/12/2019PJMFV3

 

Period Data table

ProjectPeriod StartPeriod EndPeriod Name
JOK1/11/201914/11/2019JOK PERIOD 1 (1/11/2019 - 14/11/2019)
JOK15/11/201928/11/2019JOK PERIOD 2 (15/11/2019 - 28/11/2019)
JOK29/11/201912/12/2019JOK PERIOD 3 (29/11/2019 - 12/12/2019)
JOK13/12/201926/12/2019JOK PERIOD 4 (13/12/2019 - 26/12/2019)
PJMFV2/11/201915/11/2019PJMFV PERIOD 1 (2/11/2019 - 15/11/2019)
PJMFV16/11/201929/11/2019PJMFV PERIOD 2 (16/11/2019 - 29/11/2019)
PJMFV30/11/201913/12/2019PJMFV PERIOD 3 (30/11/2019 - 13/12/2019)
PJMFV14/12/201927/12/2019PJMFV PERIOD 4 (14/12/2019 -27/12/2019)

 

Project Lookup table

ProjectName
JOKJust Okay
PJMFVPletanque Junior Malian Fifth Valedictory

 

Connections

Project in Ticket Data table is connected to Project in the Project Data table

 

Desired Output

For each Project I would like to calculate the sum of the Size of tickets started and finished within each period

 

When I filter for JOK:

PERIOD NAMESTARTFINISH
JOK PERIOD 1 (1/11/2019 - 14/11/2019)3 
JOK PERIOD 2 (15/11/2019 - 28/11/2019)3 
JOK PERIOD 3 (29/11/2019 - 12/12/2019)25
JOK PERIOD 4 (13/12/2019 - 26/12/2019)36

 

When I filter for PJMFV:

PERIOD NAMESTARTFINISH
PJMFV PERIOD 1 (2/11/2019 - 15/11/2019)3 
PJMFV PERIOD 2 (16/11/2019 - 29/11/2019)1 
PJMFV PERIOD 3 (30/11/2019 - 13/12/2019)53
PJMFV PERIOD 4 (14/12/2019 -27/12/2019) 1

 

I've been searching for an answer to this without any success. Any guidance would be greatly appreciated.

 

Thanks!

Adam

Need Help
  • VasTg's avatar
    VasTg
    6 years ago

    Anonymous 

     

    Adam,

     

    There are two aproaches to handle this.

     

    1. Covert the existing tables into below format. Create a  key combining project and Date and define relationship based on the key.

    Ticket Data Table

    TicketTypeDateProjectSize
    OK-45756Start17/12/2019JOK3
    OK-45756Finish17/12/2019JOK3
    JOK-693156Start11/11/2019JOK3
    JOK-693156Finish17/12/2019JOK3
    JOK-638156Start17/11/2019JOK3
    JOK-638156Finish9/12/2019JOK3

     

     

    Period Data Table

    ProjectPeriod NamePeriod Date
    JOK11/1/2019JOK PERIOD 1 (1/11/2019 - 14/11/2019)
    JOK11/2/2019JOK PERIOD 1 (1/11/2019 - 14/11/2019)
    JOK11/3/2019JOK PERIOD 1 (1/11/2019 - 14/11/2019)
    JOK11/4/2019JOK PERIOD 1 (1/11/2019 - 14/11/2019)
    JOK11/5/2019JOK PERIOD 1 (1/11/2019 - 14/11/2019)
    JOK11/6/2019JOK PERIOD 1 (1/11/2019 - 14/11/2019)
    JOK11/7/2019JOK PERIOD 1 (1/11/2019 - 14/11/2019)
    JOK11/8/2019JOK PERIOD 1 (1/11/2019 - 14/11/2019)
    JOK11/9/2019JOK PERIOD 1 (1/11/2019 - 14/11/2019)
    JOK11/10/2019JOK PERIOD 1 (1/11/2019 - 14/11/2019)
    JOK11/11/2019JOK PERIOD 1 (1/11/2019 - 14/11/2019)
    JOK11/12/2019JOK PERIOD 1 (1/11/2019 - 14/11/2019)
    JOK11/13/2019JOK PERIOD 1 (1/11/2019 - 14/11/2019)
    JOK11/14/2019JOK PERIOD 1 (1/11/2019 - 14/11/2019)
    PJMFV11/15/2019JOK PERIOD 2 (15/11/2019 - 28/11/2019)
    PJMFV11/16/2019JOK PERIOD 2 (15/11/2019 - 28/11/2019)
    PJMFV11/17/2019JOK PERIOD 2 (15/11/2019 - 28/11/2019)
    PJMFV11/18/2019JOK PERIOD 2 (15/11/2019 - 28/11/2019)
    PJMFV11/19/2019JOK PERIOD 2 (15/11/2019 - 28/11/2019)
    PJMFV11/20/2019JOK PERIOD 2 (15/11/2019 - 28/11/2019)
    PJMFV11/21/2019JOK PERIOD 2 (15/11/2019 - 28/11/2019)
    PJMFV11/22/2019JOK PERIOD 2 (15/11/2019 - 28/11/2019)
    PJMFV11/23/2019JOK PERIOD 2 (15/11/2019 - 28/11/2019)
    PJMFV11/24/2019JOK PERIOD 2 (15/11/2019 - 28/11/2019)
    PJMFV11/25/2019JOK PERIOD 2 (15/11/2019 - 28/11/2019)
    PJMFV11/26/2019JOK PERIOD 2 (15/11/2019 - 28/11/2019)
    PJMFV11/27/2019JOK PERIOD 2 (15/11/2019 - 28/11/2019)
    PJMFV11/28/2019JOK PERIOD 2 (15/11/2019 - 28/11/2019)

     

    2. Create two calculated columns in Period Data table as below

     

    Start Size Column =
    CALCULATE (
        SUM ( 'Ticket Data Table'[Size] ),
        FILTER (
            'Ticket Data Table',
            'Ticket Data Table'[Start] >= 'Period Data table'[Period Start]
                && 'Ticket Data Table'[Start] <= 'Period Data table'[Period End]
                && 'Period Data table'[Project] = 'Ticket Data Table'[Project]
        )
    )
     
    Finish Size Column =
    CALCULATE (
    SUM ( 'Ticket Data Table'[Size] ),
    FILTER (
    'Ticket Data Table',
    'Ticket Data Table'[Finish] >= 'Period Data table'[Period Start]
    && 'Ticket Data Table'[Finish] <= 'Period Data table'[Period End]
    && 'Period Data table'[Project] = 'Ticket Data Table'[Project]
    )
    )
     
    If this helps, mark it as a solution.
    Kudos are nice too
     

     

2 Replies

  • VasTg's avatar
    VasTg
    Icon for Memorable Member rankMemorable Member
    6 years ago

    Anonymous 

     

    Adam,

     

    There are two aproaches to handle this.

     

    1. Covert the existing tables into below format. Create a  key combining project and Date and define relationship based on the key.

    Ticket Data Table

    TicketTypeDateProjectSize
    OK-45756Start17/12/2019JOK3
    OK-45756Finish17/12/2019JOK3
    JOK-693156Start11/11/2019JOK3
    JOK-693156Finish17/12/2019JOK3
    JOK-638156Start17/11/2019JOK3
    JOK-638156Finish9/12/2019JOK3

     

     

    Period Data Table

    ProjectPeriod NamePeriod Date
    JOK11/1/2019JOK PERIOD 1 (1/11/2019 - 14/11/2019)
    JOK11/2/2019JOK PERIOD 1 (1/11/2019 - 14/11/2019)
    JOK11/3/2019JOK PERIOD 1 (1/11/2019 - 14/11/2019)
    JOK11/4/2019JOK PERIOD 1 (1/11/2019 - 14/11/2019)
    JOK11/5/2019JOK PERIOD 1 (1/11/2019 - 14/11/2019)
    JOK11/6/2019JOK PERIOD 1 (1/11/2019 - 14/11/2019)
    JOK11/7/2019JOK PERIOD 1 (1/11/2019 - 14/11/2019)
    JOK11/8/2019JOK PERIOD 1 (1/11/2019 - 14/11/2019)
    JOK11/9/2019JOK PERIOD 1 (1/11/2019 - 14/11/2019)
    JOK11/10/2019JOK PERIOD 1 (1/11/2019 - 14/11/2019)
    JOK11/11/2019JOK PERIOD 1 (1/11/2019 - 14/11/2019)
    JOK11/12/2019JOK PERIOD 1 (1/11/2019 - 14/11/2019)
    JOK11/13/2019JOK PERIOD 1 (1/11/2019 - 14/11/2019)
    JOK11/14/2019JOK PERIOD 1 (1/11/2019 - 14/11/2019)
    PJMFV11/15/2019JOK PERIOD 2 (15/11/2019 - 28/11/2019)
    PJMFV11/16/2019JOK PERIOD 2 (15/11/2019 - 28/11/2019)
    PJMFV11/17/2019JOK PERIOD 2 (15/11/2019 - 28/11/2019)
    PJMFV11/18/2019JOK PERIOD 2 (15/11/2019 - 28/11/2019)
    PJMFV11/19/2019JOK PERIOD 2 (15/11/2019 - 28/11/2019)
    PJMFV11/20/2019JOK PERIOD 2 (15/11/2019 - 28/11/2019)
    PJMFV11/21/2019JOK PERIOD 2 (15/11/2019 - 28/11/2019)
    PJMFV11/22/2019JOK PERIOD 2 (15/11/2019 - 28/11/2019)
    PJMFV11/23/2019JOK PERIOD 2 (15/11/2019 - 28/11/2019)
    PJMFV11/24/2019JOK PERIOD 2 (15/11/2019 - 28/11/2019)
    PJMFV11/25/2019JOK PERIOD 2 (15/11/2019 - 28/11/2019)
    PJMFV11/26/2019JOK PERIOD 2 (15/11/2019 - 28/11/2019)
    PJMFV11/27/2019JOK PERIOD 2 (15/11/2019 - 28/11/2019)
    PJMFV11/28/2019JOK PERIOD 2 (15/11/2019 - 28/11/2019)

     

    2. Create two calculated columns in Period Data table as below

     

    Start Size Column =
    CALCULATE (
        SUM ( 'Ticket Data Table'[Size] ),
        FILTER (
            'Ticket Data Table',
            'Ticket Data Table'[Start] >= 'Period Data table'[Period Start]
                && 'Ticket Data Table'[Start] <= 'Period Data table'[Period End]
                && 'Period Data table'[Project] = 'Ticket Data Table'[Project]
        )
    )
     
    Finish Size Column =
    CALCULATE (
    SUM ( 'Ticket Data Table'[Size] ),
    FILTER (
    'Ticket Data Table',
    'Ticket Data Table'[Finish] >= 'Period Data table'[Period Start]
    && 'Ticket Data Table'[Finish] <= 'Period Data table'[Period End]
    && 'Period Data table'[Project] = 'Ticket Data Table'[Project]
    )
    )
     
    If this helps, mark it as a solution.
    Kudos are nice too
     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks VasTg,

       

      The second approach has worked like a charm.

       

      Thanks!