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 CUSTOMERS PER WEEK
I HAVE A TROUBLE I NEED THE AVERAGE CUSTOMER'S OF ANY YEAR-NUMBER WEEK (1 TO 52), THE PERFORMANCE IT'S THAT ONLY TAKE THE DIFFERENTE CUSTOMER'S IN THE LAP ACUM, EXAMPLE: IF JIM MORTENSEN (CUSTOMER) APPEAR EN EVERY WEEK, THE DAX ONLY TAKE LIKE A 1 CUSTOMER EN X PERIOD, I NEED THAT HE COUNT SEVERAL TIMES THAT THE WEEKS HE APPEAR'S. CAN ANYBODY HELPME PLEASE.
Self-reference with DAX for Average
Hi Everyone, I have an Issue currently I have a measure called [Rate] which will be provided for actual calculated months, what I need to avhive in the [Average] Column is if the [Rate] has a value keep the rate otherwise provide an average of the last 12 months but e.g. in the table provided Nov will be the average of Nov 23 to Nov 24 [Rates] but for December I will need to take the recently calculated rate for Nov 24 and consider it for the Avg Dec 23 to Dec 24 my main issue is power BI is not self referencing the latest calculated row inted is only taking the moving average until Nov 24. Here is the Dax I'm using: DEFINE var _RollingPeriodStart = DATE(2024,3,1) var _Start = DATE(2023,1,1) var _PreviousMonth = DATE(2025,5,31) var _LastSync = [Latest Sync Date] var _Table = SUMMARIZE( FILTER( ALL('Calendar'), 'Calendar'[Date] >= _Start && 'Calendar'[Date] <= _PreviousMonth ), 'Calendar'[Year-Month], 'Calendar'[Year Month Class], "EoMDate", LASTDATE('Calendar'[Date]), "Rate", [_Rate] * 100 ) var _Table_2 = ADDCOLUMNS( _Table, "Index", RANKX( _Table, 'Calendar'[Year Month Class], , ASC, DENSE ) ) var _Table_3 = ADDCOLUMNS( _Table_2, "Average", IF( NOT(ISBLANK([Rate])), [Rate], AVERAGEX( FILTER( _Table_2, [Index] < EARLIER([Index]) && [Index] >= (EARLIER([Index]) - 12)), [Rate]))) EVALUATE _Table_3 This what need to be achieved. Instead Power BI is doing thisHow to calculate average of worktime from slicer date
Hi everyone. My problem is the present working time of an employee in the company. In the report, it has a date slicer, a count of working days, and the working hour of the employee in duration time. Currently, I want to present average of working day and average working hour in duration time from date slicer. My data below Table 'working_time' has columns: emloyee_id, workingdate, worktime_hrs (worktime hour in day), worktime_day (worktime day has default is 1) Depend on table sum worktime group by employee, the sum worktime is the total value divided by 5 employees. My expectation is the average of worktime_day should be (3+3+2+2+3) / 5 = 2.6 and the average of worktime_hrs should be (24+24+16+16+24) / 5 = 20.8. But I don't know how to calculate this average value. I hope everyone will spare their precious time to help me solve this problem. Thanks you so much!
How to calculate average
Hi, I have 2 sheets, Sheet 1 – Result of evaluation – there is only 1 record per 1 person, and everyone has the evaluation result on the same date – 31/01/24 Example of raw data Units Result of evaluation Date Marketing 1 31/01/24 Marketing 3 31/01/24 Finance 2 31/01/24 Finance 2 31/01/24 Finance 3 31/01/24 Operations 3 31/01/24 Operations 3 31/01/24 Operations 4 31/01/24 Sheet 2- List of resign employees – which shows the ‘Result of evaluation’ that was merged from the sheet 1. Example of raw data Units Employee ID Result of evaluation Terminate Date Marketing 1111 1 31/01/24 Finance 3333 2 15/03/24 There is no relationship between these 2 sheets because once I create the relationship it said that it will cause ambiguity. And the one result that I’ve been longing for is to calculate the average of Result of evaluation so that I can calculate the attrition rate after this. This is what I have on Excel Sum of Result of evaluation 1-3 (4 was not counted) January - number of terminations in Jan No termination in February March - number of terminations in March Units January February March April Average Marketing 2 1 1 1 1.25 Finance 3 3 3 2 2.75 Operations 2 2 2 2 2 So after I got the average I can calculate the attrition rate like this (number of terminations / average) Units January February March April Marketing 50% (1/2) 67% (1/1.5) 75% (1/1.33) 80% (1/1.25) Finance 0% 0% 33% (1/3) 36% (1/2.75) Operations 0% 0% 0% 0% But now the current measure I got brings me the wrong answers, it's only correct when at the end of the year (e.g. only the attrition rate in December is correct) So please help suggest how to calculate the attrition like above table. Thank you in advance. 🙏🙏
12 Week Rolling Average without Dates
Good morning and Happy New Year, I have been wracking my brains for a while now on how to solve a problem. What I need to do is create a rolling 12 week average, which I seem to have done but the problem lies with the initial 12 weeks of the selected financial year. Rather then looking back at the 12 weeks in the previous financial year, its start off at week 1 and accumulates the average until it gets to week 12. Hopefully the visual below explains it better. Ideally, not sure if its possible in Power Bi to generate values in years that arent selected in the slicers, but I would want week 1 to be an average of the previous 12 weeks in the last financial year even if its not been selected so the user can select a year at a time rather then multiples. I have put the DAX below. _12W_RA = VAR CurrentWeek = MAX(DIM_CALENDAR[WEEK_OFFSET]) RETURN CALCULATE( SUMX( FILTER( ALLSELECTED(FCT_LCHID), FCT_LCHID[DIM_CALENDAR.WEEK_OFFSET] <= CurrentWeek && FCT_LCHID[DIM_CALENDAR.WEEK_OFFSET] >= CurrentWeek - 11 ), FCT_LCHID[LOST_CUSTOMER_HOURS] ) )/12 Any help would be great. Thanks Rich
Having Trouble Figuring the Average Turnaround Time
Hi, several weeks ago EylesIT helped me big time by solving for me the calculation of the time difference between the completion of one task in our files to the completion of a second task in our file. Now, I'd like to solve for the average time for each task category. I'll try and lay it out to help you understand it better. We're an insurance agency with one file for each property. Each file has multiple tasks that have to be completed. Each task has a "completed/received date" in the database. Therefore, each file will have multiple completed/received dates. What EylesIT helped with was figuring the time difference out, by using a series of measures, shown here: Completed-AdvOrdEnt = VAR orderno = SELECTEDVALUE(SPSTask[Order Number]) RETURN CALCULATE( MAX(SPSTask[Completed/Received Date]), SPSTask[Order Number] = orderno, SPSTask[TaskLookupCode] = "AdvOrdEnt" ) ----------------------- BscOrd to AdvOrd = DATEDIFF([Completed-BscOrdEnt], [Completed-AdvOrdEnt], SECOND) ----------------------- BscOrd to AdvOrd DHMS = IF([BscOrd to AdvOrd]>0, VAR DAXDay = INT([BscOrd to AdvOrd]/(24*60*60)) VAR DAXHours = MOD(INT([BscOrd to AdvOrd]/(60*60)),24) VAR DAXMin = MOD(INT([BscOrd to AdvOrd]/60),60) VAR DAXSec = MOD( [BscOrd to AdvOrd],60) RETURN DAXDay & "days | " & DAXHours & "h | " & DAXMin & "m | " & DAXSec & "s") ------------------------- My output looks like this, which is fabulous: What I'm trying to do is take the sum of the second measure, where it's calculating the datediff in seconds, and then divide it by the number of specific task completions during the time period I'm looking at. I've tried several different things and can't get anything to work.
