Forum Discussion
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 Ticket Start Finish Project Size JOK-45756 17/12/2019 17/12/2019 JOK 3 JOK-693156 11/11...
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
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
VasTg
Memorable Member
Memorable MemberAnonymous
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]
)
)
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]
)
)
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