Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jereaallikko
Helper III
Helper III

Help with capacity calculations per logged work hours and period

Hi all,

 

I am trying to create a table, charts etc visuals showing the logged work & capacity per wanted timeframe. My real dataset contains hundreds of records.

 

Here is a sample data, with three different tables:

  • Period, to define the current period, start & end date, total capacity in use for period

Period TablePeriod Table

 

  • Work log, to show hours logged, date, employee & item key

Work Log TableWork Log Table

  • Data, containing employee, item key, tag & item creation date

Data TableData Table

 

 

 

 

How can I define the Period in Work log table, based on the date? For instance Item Work_221 (date 4.6.2020) lands to Period 2006 in Period table and Work_210 (date 13.2.2020) falls in 2002.

 

The main goal is to create a chart, where X-axis is "Period" (or year/month), values sum of "Hours" and line (if possible) the "Capacity, hours". 

 

It should form a graph like this:

Chart.JPG

 

 

 

 

 

 

 

 

 

 

 

 

I formed this graph manually, not possible with real data due to the amount of records.

 

Thanks in advance.

 

Any tips?

 

Br, 

Jere

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@jereaallikko , Assume period is unique in first table. Create a new column in DAX based on the date column in table and get period and then join on period with period table 

In 2nd and 3rd table, new column

 

Period = maxx(filter(Period, 'Work Log'[Date] >= Period[Start Date] && 'Work Log'[Date] <= Period[End Date]), Period[Period])

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@jereaallikko , Assume period is unique in first table. Create a new column in DAX based on the date column in table and get period and then join on period with period table 

In 2nd and 3rd table, new column

 

Period = maxx(filter(Period, 'Work Log'[Date] >= Period[Start Date] && 'Work Log'[Date] <= Period[End Date]), Period[Period])

Hi @amitchandak 

 

That solved the problem. Thanks a lot!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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