Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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:
I formed this graph manually, not possible with real data due to the amount of records.
Thanks in advance.
Any tips?
Br,
Jere
Solved! Go to Solution.
@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])
@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])
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |