Calculate Sum in Period Where Periods Differ By Project

[AdaminMelbourne]

Hello,

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

Ticket Data table

Ticket	Start	Finish	Project	Size
JOK-45756	17/12/2019	17/12/2019	JOK	3
JOK-693156	11/11/2019	17/12/2019	JOK	3
JOK-638156	17/11/2019	9/12/2019	JOK	3
JOK-481156	3/12/2019	9/12/2019	JOK	2
PJMFV-100	1/12/2019		PJMFV	5
PJMFV-154	25/11/2019	16/12/2019	PJMFV	1
PJMFV-57	11/11/2019	3/12/2019	PJMFV	3

Period Data table

Project	Period Start	Period End	Period Name
JOK	1/11/2019	14/11/2019	JOK PERIOD 1 (1/11/2019 - 14/11/2019)
JOK	15/11/2019	28/11/2019	JOK PERIOD 2 (15/11/2019 - 28/11/2019)
JOK	29/11/2019	12/12/2019	JOK PERIOD 3 (29/11/2019 - 12/12/2019)
JOK	13/12/2019	26/12/2019	JOK PERIOD 4 (13/12/2019 - 26/12/2019)
PJMFV	2/11/2019	15/11/2019	PJMFV PERIOD 1 (2/11/2019 - 15/11/2019)
PJMFV	16/11/2019	29/11/2019	PJMFV PERIOD 2 (16/11/2019 - 29/11/2019)
PJMFV	30/11/2019	13/12/2019	PJMFV PERIOD 3 (30/11/2019 - 13/12/2019)
PJMFV	14/12/2019	27/12/2019	PJMFV PERIOD 4 (14/12/2019 -27/12/2019)

Project Lookup table

Project	Name
JOK	Just Okay
PJMFV	Pletanque 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 NAME	START	FINISH
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)	2	5
JOK PERIOD 4 (13/12/2019 - 26/12/2019)	3	6

When I filter for PJMFV:

PERIOD NAME	START	FINISH
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)	5	3
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

[VasTg · Accepted solution]

@AdaminMelbourne 

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

Ticket	Type	Date	Project	Size
OK-45756	Start	17/12/2019	JOK	3
OK-45756	Finish	17/12/2019	JOK	3
JOK-693156	Start	11/11/2019	JOK	3
JOK-693156	Finish	17/12/2019	JOK	3
JOK-638156	Start	17/11/2019	JOK	3
JOK-638156	Finish	9/12/2019	JOK	3

Period Data Table

Project	Period Name	Period Date
JOK	11/1/2019	JOK PERIOD 1 (1/11/2019 - 14/11/2019)
JOK	11/2/2019	JOK PERIOD 1 (1/11/2019 - 14/11/2019)
JOK	11/3/2019	JOK PERIOD 1 (1/11/2019 - 14/11/2019)
JOK	11/4/2019	JOK PERIOD 1 (1/11/2019 - 14/11/2019)
JOK	11/5/2019	JOK PERIOD 1 (1/11/2019 - 14/11/2019)
JOK	11/6/2019	JOK PERIOD 1 (1/11/2019 - 14/11/2019)
JOK	11/7/2019	JOK PERIOD 1 (1/11/2019 - 14/11/2019)
JOK	11/8/2019	JOK PERIOD 1 (1/11/2019 - 14/11/2019)
JOK	11/9/2019	JOK PERIOD 1 (1/11/2019 - 14/11/2019)
JOK	11/10/2019	JOK PERIOD 1 (1/11/2019 - 14/11/2019)
JOK	11/11/2019	JOK PERIOD 1 (1/11/2019 - 14/11/2019)
JOK	11/12/2019	JOK PERIOD 1 (1/11/2019 - 14/11/2019)
JOK	11/13/2019	JOK PERIOD 1 (1/11/2019 - 14/11/2019)
JOK	11/14/2019	JOK PERIOD 1 (1/11/2019 - 14/11/2019)
PJMFV	11/15/2019	JOK PERIOD 2 (15/11/2019 - 28/11/2019)
PJMFV	11/16/2019	JOK PERIOD 2 (15/11/2019 - 28/11/2019)
PJMFV	11/17/2019	JOK PERIOD 2 (15/11/2019 - 28/11/2019)
PJMFV	11/18/2019	JOK PERIOD 2 (15/11/2019 - 28/11/2019)
PJMFV	11/19/2019	JOK PERIOD 2 (15/11/2019 - 28/11/2019)
PJMFV	11/20/2019	JOK PERIOD 2 (15/11/2019 - 28/11/2019)
PJMFV	11/21/2019	JOK PERIOD 2 (15/11/2019 - 28/11/2019)
PJMFV	11/22/2019	JOK PERIOD 2 (15/11/2019 - 28/11/2019)
PJMFV	11/23/2019	JOK PERIOD 2 (15/11/2019 - 28/11/2019)
PJMFV	11/24/2019	JOK PERIOD 2 (15/11/2019 - 28/11/2019)
PJMFV	11/25/2019	JOK PERIOD 2 (15/11/2019 - 28/11/2019)
PJMFV	11/26/2019	JOK PERIOD 2 (15/11/2019 - 28/11/2019)
PJMFV	11/27/2019	JOK PERIOD 2 (15/11/2019 - 28/11/2019)
PJMFV	11/28/2019	JOK 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

[AdaminMelbourne]

Thanks @VasTg,

The second approach has worked like a charm.

Thanks!