User Profile
jen8080
Helper I
Joined 5 years ago
User Widgets
Contributions
Rolling 3 Month Average Help QUICK
Hello, I have this data and a need a DAX formula to show the rolling 3 month average on the graph based on date 2 for the x-axis and Days between on the Y-axis. For Example in Feb22 the Average days for a rolling 3 months would be 29.71 (Dec, Jan, Feb) THANK YOU! Year Month Date 1 Date 2 Days between Date 1 and 2 Project ID 2021 October 9/10/2021 0:00 10/4/2021 0:00 24 T8 2021 October 9/23/2021 0:00 10/12/2021 0:00 19 T2 2021 November 10/25/2021 0:00 11/4/2021 0:00 10 E2 2021 November 11/4/2021 0:00 11/18/2021 0:00 14 S1 2021 November 10/6/2021 0:00 11/19/2021 0:00 44 T1 2021 December 11/17/2021 0:00 12/14/2021 0:00 27 B8 2021 December 11/9/2021 0:00 12/15/2021 0:00 36 GW 2022 January 12/1/2021 0:00 1/5/2022 0:00 35 B9 2022 January 11/22/2021 0:00 1/21/2022 0:00 60 C3 2022 February 1/17/2022 0:00 2/1/2022 0:00 15 C1 2022 February 1/19/2022 0:00 2/1/2022 0:00 13 KC 2022 February 1/11/2022 0:00 2/2/2022 0:00 22 M2Gantt Visual Help
Hi!! I am trying to build a gnatt based on this table below. I want it to look similar to this photo but I need symbols on the rows for the 'Out and In Dates" but want to keep the bar I already have. I tried added the 'In and out' date columns to milestones and this didn't work. Please note I can't attach real data as it is senstive. Any help?? Client Project ID Milestone Target Date Projected Date Actual Date Projected Dt if blank Target Dt Live Start Lock Start In Proj Dt if blank Target Dt Out Proj Dt if blank Target Dt start date Client 1 Project 1 Lock 19-Jan-24 16-Apr-24 16-Apr-24 17-Mar-24 17-Mar-24 Client 1 Project 1 Out 29-Sep-23 27-Dec-23 27-Dec-23 27-Dec-23 Client 1 Project 1 In 6-Dec-21 6-Dec-21 6-Dec-21 Client 1 Project 1 Live 22-Nov-21 1-Dec-21 1-Dec-21 18-Aug-21 18-Aug-21 Client 2 Project 2 Out 18-Jul-24 25-Nov-25 25-Nov-25 25-Nov-25 Client 2 Project 2 In 15-Aug-19 16-Sep-19 16-Sep-19 16-Sep-19 16-Sep-19 Client 2 Project 2 Live 6-Aug-19 13-Aug-19 13-Aug-19 13-Aug-19 30-Apr-19 30-Apr-19 Client 2 Project 2 Lock 20-Aug-24 22-Dec-25 22-Dec-25 23-Nov-25 23-Nov-25 Client 2 Project 2 Out 2-Oct-24 19-Feb-26 19-Feb-26Creating a Status bar|| Help!
Hello- This seems like it should be very simple with a helper table and a couple measures but I am not having any luck. Goal: Create a status bar and show the previous statuses were met. Here is what it should look like: Status order: Status Sort order Identified 1 Defined 2 Extracted 3 Formatted 4 Approved 5 Transferred 6 N/A 7 Data: Company Country Product Status Status Date Company Name 1 Spain Product 1 Approved 1/1/2021 Company Name 1 Italy Product 2 Transferred 1/2/2021 Company Name 1 France Product 3 Transferred 1/3/2021 Company Name 1 UK Product 4 Transferred 1/4/2021 Company Name 1 Netherlands Product 5 Transferred 1/5/2021 Company Name 1 Montenegro Product 6 Transferred 1/6/2021 Company Name 1 Croatia Product 7 Transferred 1/7/2021 Company Name 1 Spain Product 8 Transferred 1/8/2021 Company Name 1 Italy Product 9 Transferred 1/9/2021 Company Name 1 France Product 10 Transferred 1/10/2021 Company Name 1 UK Product 11 Transferred 1/11/2021 Company Name 1 Netherlands Product 12 Transferred 1/12/2021 Company Name 1 Montenegro Product 13 Approved 1/13/2021 Company Name 1 Croatia Product 14 Transferred 1/14/2021 Company Name 2 Australia Product 15 Transferred 1/15/2021 Company Name 2 New Zealand Product 16 Transferred 1/16/2021 Company Name 2 Fiji Product 17 Formatted 1/17/2021 Company Name 2 Samoa Product 18 Extracted 1/18/2021 Company Name 2 Tonga Product 19 Extracted 1/19/2021 Company Name 2 Australia Product 20 Extracted 1/20/2021 Company Name 2 New Zealand Product 21 Extracted 1/21/2021 Company Name 2 Fiji Product 22 Transferred 1/22/2021 Company Name 2 Samoa Product 23 Transferred 1/23/2021 Company Name 2 Tonga Product 24 Transferred 1/24/2021Help! Dynamic measure based on start and end dates!
I'm trying to figure out how to spread the requested hours for a project over the length of that project with a known start and end date. I need it to be dynamic enough to understand if it started mid-month it would then have less hours then if it was a full month. The end goal is to get a forecast of how many hours we expect in each month for all the projects we have to determine the overall capacity demands for the month. So we'd have something like a bar chart that shows the total hours demand for each month based on the projects that will impact that month. Example: requested hours is 80 the estimated work start date in 15-Jun-20 and Estimated work end date is 24-Aug-20 the measure would span the requested hours our accordingly. Jun-20 Jul-20 Aug-20 19 35 26 Notes: 1. I have already created a separate date table called "Calendar" with these columns Calendar = CALENDAR(MIN(report[Estimated Work Start Date]),MAX(report[Estimated Work End Date])) MONTHYEAR = MONTH('Calendar'[Date])/10+YEAR('Calendar'[Date]) 2. I already have a measure that spans data out evenly per month vs what I need which is for it to be dynamic based on dates Measure 2 = SUMX(VALUES('Calendar'[MONTHYEAR]),CALCULATE(SUM(report[HoursPerMonth]),FILTER('report',var a = 'report'[Estimated Work Start Date]var b = 'report'[Estimated Work End Date] return MONTH(a)/10+YEAR(a)<= MAX('Calendar'[MONTHYEAR])&&MONTH(b)/10+YEAR(b)>= MAX('Calendar'[MONTHYEAR])))) 3.. Each project can have multiple requested hours (that’s why the same project is listed multiple times) I have a dynamic key get a unique ID by row 4. I cannot share the data it is sensitive but I have created a sample set below Project Requested Hours Estimated Work End Date Estimated Work Start Date Work Start Vs Work End Days Requested Hours per Day HoursPerMonth KEY Project 1 743.7 30-Apr-21 31-Jul-20 274 2.71 74.37 Key 1 Proejct 2 60 19-Jun-20 1-Jun-20 19 3.16 60 Key 2 Project 3 138 1-Jan-21 1-Jun-20 215 0.64 17.25 Key 3 Project 4 10 20-Aug-21 22-Jun-20 425 0.02 0.67 Key 4 Project 4 2 20-Aug-21 22-Jun-20 425 0 0.13 Key 5 Project 5 72 12-Oct-20 27-Jul-20 78 0.92 18 Key 6 Project 5 90 12-Oct-20 27-Jul-20 78 1.15 22.5 Key 7 Project 5 40 12-Oct-20 27-Jul-20 78 0.51 10 Key 8 Project 5 48 12-Oct-20 27-Jul-20 78 0.62 12 Key 9 Project 5 24 12-Oct-20 27-Jul-20 78 0.31 6 Key 10 Project 5 390 12-Oct-20 27-Jul-20 78 5 97.5 Key 11 Project 5 16 12-Oct-20 27-Jul-20 78 0.21 4 Key 12 Project 5 4 12-Oct-20 27-Jul-20 78 0.05 1 Key 13 Project 5 8 12-Oct-20 27-Jul-20 78 0.1 2 Key 14 Project 5 16 12-Oct-20 27-Jul-20 78 0.21 4 Key 15 Project 5 50 12-Oct-20 27-Jul-20 78 0.64 12.5 Key 16 Project 5 4 12-Oct-20 27-Jul-20 78 0.05 1 Key 17 Project 5 12 12-Oct-20 27-Jul-20 78 0.15 3 Key 18 Project 5 8 12-Oct-20 27-Jul-20 78 0.1 2 Key 19 Project 5 45.5 12-Oct-20 27-Jul-20 78 0.58 11.38 Key 20 Project 5 124.2 12-Oct-20 27-Jul-20 78 1.59 31.05 Key 21 Project 5 39 12-Oct-20 27-Jul-20 78 0.5 9.75 Key 22 Project 5 8 12-Oct-20 27-Jul-20 78 0.1 2 Key 23 Project 5 30 12-Oct-20 27-Jul-20 78 0.38 7.5 Key 24 Project 5 340 12-Oct-20 27-Jul-20 78 4.36 85 Key 25 Project 5 68 12-Oct-20 27-Jul-20 78 0.87 17 Key 26 Project 5 40 12-Oct-20 27-Jul-20 78 0.51 10 Key 27 Project 6 7.5 5-Jun-20 1-Jun-20 5 1.5 7.5 Key 28 Project 6 4.5 5-Jun-20 1-Jun-20 5 0.9 4.5 Key 29 Project 7 4 17-Jul-20 14-Jul-20 4 1 4 Key 30 Project 7 10 17-Jul-20 14-Jul-20 4 2.5 10 Key 31 Project 7 6 17-Jul-20 14-Jul-20 4 1.5 6 Key 32 Project 7 5 17-Jul-20 14-Jul-20 4 1.25 5 Key 33 Project 7 2 17-Jul-20 14-Jul-20 4 0.5 2 Key 34 Project 8 10 1-Jul-22 1-Jul-20 731 0.01 0.4 Key 35 Project 9 0.5 30-Jun-20 26-Jun-20 5 0.1 0.5 Key 36 Project 9 1 30-Jun-20 26-Jun-20 5 0.2 1 Key 37 Project 10 2 30-Jul-20 27-Jul-20 4 0.5 2 Key 38 Project 10 10 31-Jul-20 8-Jul-20 24 0.42 10 Key 39 Project 10 3 19-Jun-20 8-Jun-20 12 0.25 3 Key 40Re: How to divide hours for a project over start and end dates|| Urgent
Hi v-deddai1-msft, Is there a measure to span this based on days, but then still review it in the month format? Also how would I account if an estimated work start date started mid month? Is there a way to make the daily or monthly amount adjusted based on when the work starts or ends in the month? Thanks for the help!!
