Datesinperiode / Display data over 12 month

[Do57792]

Hello Experts,

I have the following problem. The duration of building the houses is always a year. I want to display a table ( and later graphs) like the sum of the needed workers over the year for every month.

Main Data:

House	CompletionDatePlanned	CompletionDatePlanned2	Workers
1	01. Jan 2023	01.Feb 2024	5
2	01. Jul 2022	01.Sep 2023	7

Now I want to have a  table like this:

Month	Jan 2023	Feb 2023	Mar 2023	... 2023	Jun 2024	Jul 2023	... 2023	Dec 2023	Jan 2024	Feb 2024	Mar 2024	...	Aug 2024	Sep 2024	...
Workers CompletionDatePlanned	13	13	13	13	13	5	5	5	0	0	0	0	0	0
Workers CompletionDatePlanned 2	0	0	0	0	0	0	7	7	7	13	13	13	13	5
Sum	13	13	13	13	13	5	13	13	7	13	13	13	13	5

I have only one column of completition for date 1 and date 2. I dont want to transpond the colums and rows, otherwise my rest of the visauls arent working anymore.
Now my problem is the following: I have a created calendar which respond to completion planned1. For this my formular is working fine and it display the workers for the 12 month of the year. The Month year correspond to this table. To get the same result for CompletionDatePlanned2 i have to use another connection between the tables and need to remove existing ones. How do I need to do this?

Could you please help me?

Existing Formular for Workers CompletionDatePlanned

calculate(sumx('Main Data','TMain Data'[Workers]),DATESINPERIOD(Calendar_Planned1[Date], max(Calendar_Planned1[Date]), -1,year) )

[Do57792]

One additional question: How can I get the cumulative number, which means:

Jan 2023	Feb 2023	Mar 2023	... 2023	Jun 2024	Jul 2023	... 2023
13	26	39	...	78	83	....

I tried to use the the Calendar, but the connection between the tables is from date to date and the workers are for month. If I use a virtual table and add my workers as a seperate column to sum up this one, the result is wrong 😑

[Do57792]

Many thanks!

[mickey64 · Accepted solution]

For your reference.

Step 1: I add some relationships below.

Step 2: I make 3 measures.

M_Sum = calculate(sumx('Main Data','Main Data'[Workers]),DATESINPERIOD(Calendar[Date], max(Calendar[Date]), -1,year) )

M_Sum2 = calculate(sumx('Main Data','Main Data'[Workers]),USERELATIONSHIP('Main Data'[CompletionDatePlanned2],'Calendar'[Date]),DATESINPERIOD(Calendar[Date], max(Calendar[Date]), -1,year) )

M_Sum 1_2 = [M_Sum]+[M_Sum2]

Step 3: I make a table below.

[rajendraongole1]

Hi @Do57792 - can you try the below measure for workers CompletionDatePlanned and CompletionDatePlanned2:
Define measures to calculate the number of workers based on CompletionDatePlanned and CompletionDatePlanned2.

WorkersCompletionDatePlanned =
CALCULATE(
SUM('Main Data'[Workers]),
DATESINPERIOD(
'Calendar'[Date],
MAX('Calendar'[Date]),
-1,
YEAR
)
)

WorkersCompletionDatePlanned2 =
CALCULATE(
SUM('Main Data'[Workers]),
DATESINPERIOD(
'Calendar'[Date],
MAX('Calendar'[Date]),
-1,
YEAR
),
USERELATIONSHIP('Main Data'[CompletionDatePlanned2], 'Calendar'[Date])
)

Create a measure to sum the results of the two previous measures

TotalWorkers =
[WorkersCompletionDatePlanned] + [WorkersCompletionDatePlanned2]

Hope it helps