Issue with rolling average based off item type
Hello, I'm looking to create a rolling 90 day/3 month average of LeadTime over the course of a year. This is what my data looks like The issue with this is I have three different WorkItemType values: "Task", "Feature" and "PBI" so even though all the data is in a single table, I need three different rolling averages, one per type. The DAX I have so far is for a single type, Feature. I assume I will need 3 measurements in total, one for each. Rolling Average = VAR LastTransactionDate = MAX('Query1'[CompletedDateSK]) VAR AverageDay = 90 VAR PeriodInVisual = FILTER( ALL( 'Query1'[CompletedDateSK] ), AND( 'Query1'[CompletedDateSK] > LastTransactionDate - AverageDay, 'Query1'[CompletedDateSK] <= LastTransactionDate ) ) VAR WorkItemType = FILTER( ALL( 'Query1'[WorkItemType] ), AND( 'Query1'[WorkItemType] = "Feature", 'Query1'[WorkItemType] = "Feature" ) ) VAR OutPut = CALCULATE( AVERAGEX( 'Query1', [LeadTimeDays] ), PeriodInVisual, WorkItemType ) RETURN OutPut Which feels like it can be improved. I'm also not sure it is calculating the right values either, since the rolling average is being calculcated as the same as the month average How can I calculate rolling average correctly given my data? Thanks!
Average time taken in last 7/14/30/90/365 days
Hi, I am trying to calculate how long it has taken to approve/reject work orders in the last 7/14/30/90/365 days in power bi to show something like this: e.g. in the last 7 days it has taken an everage of 3 days to approve/reject works relating to Autodoors. The 7/14/30/90/365 dates would need to be based of the latest 'date created' in the data table my data table has these headers: is this possible? Thanks
3 sprint rolling average for throughput
Hi everyone, I'm new in this community and I've been struggling for a couple of weeks because I'm stuck in this problem. My goal is to calculate a 3 sprint rolling average for the sum of story-points completed. I'm not sure if a DAX measure will work or rather a calculated column. I'm open to any workarounds. Data tables SprintReportIssues which is the principal table I'm using to produce all the visuals DateSprint which is a reference table I created for filtering/labeling purposes Boards which I'm using for the slicer and to filter by BOARD_NAME I've produced some images to explain the problem SprintReportIssues The data has many boards (BOARD_ID and BOARD_NAME), that are composed by many sprints (SPRINT_ID and SPRINT_NAME) which have many issues (ISSUE_ID and ISSUE_KEY). POINTS COMPLETED is a measure I created to calculate the sum of CURRENT_ESTIMATE if SPRINT_REPORT_STATUS = "COMPLETED' DateSprint I'm using this table to group sprints by their dates (DATE). In DateSprint, DATE = START_DATE in SprintReportIssues. REFERENCE is just my preferred way of labeling Matrix of Interest You can see that the visual is summarizing POINTS COMPLETED by: Rows: SPRINT ('DateSprint'[REFERENCE]) and SPRINT_NAME ('SprintReportIssues'[SPRINT_NAME]) Values: START_DATE ('DateSprint'[DATE]) which I use to sort and POINTS COMPLETED('SprintReportIssues'[POINTS COMPLETED]) Expected Outcome 1 I want a 3-sprint Rolling Average per REFERENCE. Usually sprints are 15 days so we'd be looking at the current sprint that we want to calculate and ~30 days back Expected Outcome 2 Ideally, with this Rolling Average I'd be able to filter by BOARD_NAME using the slicer and we'd be able to see Rolling Averages a level down (per board) Complexity There are 36 boards and 871 sprints for now. In the images above the measures are grouped by board but in the future I'm looking to have the capability of grouping boards by some other reference that I will call "X" for now. Hierarchy: Highest Level Lowest Level X > Boards > Sprints Sample Data Reminder: POINTS COMPLETED is a measure SPRINT SPRINT_NAME Earliest DATE POINTS COMPLETED 2023.01.3 2023.01.3 - Analytics 2/8/2023 0:00 21 2023.01.3 2023.01.3 - Data Product 2/8/2023 0:00 18 2023.01.3 2023.01.3 - EDW Replatform 2/8/2023 0:00 75 2023.01.3 2023.01.3 - On Prem Data WH 2/8/2023 0:00 6 2023.01.3 2023.01.3 -Data Mgmt & Arch 2/8/2023 0:00 26 2023.01.2 2023.01.2 - Analytics 1/25/2023 0:00 18 2023.01.2 2023.01.2 - Data Product 1/25/2023 0:00 18 2023.01.2 2023.01.2 - EDW Replatform 1/25/2023 0:00 91 2023.01.2 2023.01.2 - On Prem Data WH 1/25/2023 0:00 13 2023.01.2 2023.01.2 -Data Mgmt & Arch 1/25/2023 0:00 40 2023.01.1 2023.01.1 - Analytics 1/11/2023 0:00 22 2023.01.1 2023.01.1 - Data Product 1/11/2023 0:00 18 2023.01.1 2023.01.1 - EDW Replatform 1/11/2023 0:00 84 2023.01.1 2023.01.1 - On Prem Data WH 1/11/2023 0:00 24 2023.01.1 2023.01.1 -Data Mgmt & Arch 1/11/2023 0:00 6 PI6 - IP PI6 - IP - Analytics 12/28/2022 0:00 12 PI6 - IP PI6 - IP - Data Mgmt & Arch 12/28/2022 0:00 12 PI6 - IP PI6 - IP - EDW Replatform 12/28/2022 0:00 112 PI6 - IP PI6 - IP - On-Prem Data WH 12/28/2022 0:00 15 PI6 - IP PI6 - IP- Data Product 12/28/2022 0:00 26 PI6 - S5 PI6 - S5 - Analytics 12/14/2022 0:00 7 PI6 - S5 PI6 - S5 - Data Mgmt & Arch 12/14/2022 0:00 11 PI6 - S5 PI6 - S5 - EDW Replatform 12/14/2022 0:00 19 PI6 - S5 PI6 - S5 - On-Prem Data WH 12/14/2022 0:00 10 PI6 - S5 PI6 - S5- Data Product 12/14/2022 0:00 13 PI6 - S4 PI6 - S4 - Data Mgmt & Arch 12/1/2022 0:00 15 PI6 - S4 PI6 - S4 - Analytics 12/1/2022 0:00 27 PI6 - S4 PI6 - S4 - EDW Replatform 12/1/2022 0:00 50 PI6 - S4 PI6 - S4 - On-Prem Data WH 12/1/2022 0:00 24 PI6 - S4 PI6 - S4- Data Product 12/1/2022 0:00 8 PI6 - S3 PI6 - S3 - Data Mgmt & Arch 11/16/2022 0:00 3 PI6 - S3 PI6 - S3 - EDW Replatform 11/16/2022 0:00 60 PI6 - S3 PI6 - S3 - On-Prem Data WH 11/16/2022 0:00 28 PI6 - S3 PI6 - S3- Analytics 11/16/2022 0:00 16 PI6 - S3 PI6 - S3- Data Product 11/16/2022 0:00 9 PI6 - S2 PI6 - S2 - Data Mgmt & Arch 11/2/2022 0:00 PI6 - S2 PI6 - S2 - Analytics 11/2/2022 0:00 14 PI6 - S2 PI6 - S2 - EDW Replatform 11/2/2022 0:00 25 PI6 - S2 PI6 - S2 - On-Prem Data WH 11/2/2022 0:00 4 PI6 - S2 PI6 - S2- Data Product 11/2/2022 0:00 17 PI6 - S1 PI6 - S1 - Data Mgmt & Arch 10/19/2022 0:00 5 PI6 - S1 PI6 - S1 - Analytics 10/19/2022 0:00 6 PI6 - S1 PI6 - S1 - EDW Replatform 10/19/2022 0:00 36 PI6 - S1 PI6 - S1 - On-Prem Data WH 10/19/2022 0:00 9 PI6 - S1 PI6 - S1- Data Product 10/19/2022 0:00 19 Please help! I've been stuck for so long in this problem and I'm not sure if it cannot be solved because of the sprint order and the dates, or if there's another impediment.
Monthly Comparison with yearly average
I need to create a table or matrix that breaks the data into 4 columns: this month, last month, average of last 6 months, and avg of last 12 months. What is the best way to go about this? I tried grouping the data, but the groups can't overlap (ie 4/20/22 would need to be included in last month, last 6 months, and last 12 months. I also tried this formula Last month = CALCULATE('Charge Data'[Total Charges],dateadd('Charge Data'[Date Satisfied],-1,MONTH)) But adding that measure to the matrix values just includes it as a new row... If that makes any sense? Any and all help is appreciated!
12-month rolling average without dividing the values that are blank.
Hi, My question is particularly with the 12-month rolling average. What I did was I utilized the 'Quick Measure' feature of PowerBI and selected the corresponding fields to be calculated for the 12-months. Below was the automatic formula created. RAve_Time to Hire = IF( ISFILTERED('Date'[Date]), ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."), VAR __LAST_DATE = ENDOFMONTH('Date'[Date].[Date]) VAR __DATE_PERIOD = DATESBETWEEN( 'Date'[Date].[Date], STARTOFMONTH(DATEADD(__LAST_DATE, -12, MONTH)), __LAST_DATE ) RETURN AVERAGEX( CALCULATETABLE( SUMMARIZE( VALUES('Date'), 'Date'[Date].[Year], 'Date'[Date].[QuarterNo], 'Date'[Date].[Quarter], 'Date'[Date].[MonthNo], 'Date'[Date].[Month] ), __DATE_PERIOD ), CALCULATE(SUM('MP - Engine'[Time to Hire]), ALL('Date'[Date].[Day])) ) ) This totally worked for those that have values per month. However, the problem takes place when the months are blank or have no values. For example, the rolling average below should be 100%, but since it was counting those months that had no values/blank, it is getting different rolling average totals. How do I solve this? I am thinking of filtering or placing an IF statement where only those months that have values will be calculated as opposed to dividing it to 12. However, I am not sure how to add this to the code above.
