cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Datesinperiode / Display data over 12 month

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?

Existing Formular for Workers CompletionDatePlanned

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

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.

4 REPLIES 4
Frequent Visitor

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 😑

Frequent Visitor

Many thanks!

Super User

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.

Super User

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

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors