measure average age per order
Hi, in my powerbi report I have this measure called "# Open Orders EOP" where I measure how many open orders we have per week. Now I also want to add the average age per open order. The result should look like column E from the screenshot below. Column A, B, C and D is what I have in my powerbi report, but I want to add column E (the individual values are maybe not correct). measure1 OpenOrdersWoW = VAR MinDate = MIN ( 'dim_date'[Date] ) VAR MaxDate = MAX ( 'dim_date'[Date] ) VAR Result = CALCULATE ( COUNTROWS ( tableA ), tableA[CreatedDate] <= MaxDate, OR( tableA[ClosedDate] > MinDate, ISBLANK(tableA[ClosedDate]) ), REMOVEFILTERS ( 'dim_date' ) ) RETURN Result measure2 (column D) # Open Orders EOP = CALCULATE ( [OpenOrdersWoW], LASTDATE ( 'dim_date'[Date] ) )
Total average of last 12 months
I've been trying to get the right measure for what I am looking for with no luck. When I bring in Report Date and Sum of Gross Dollars, I am simply trying to get the total average ($4,346,951) into a card visual, like I see in Excel when I export the data: I've tried this DAX but get a total of $4,358,088 (close but no match) 12mthavginv = VAR CurrentDate = MAX('Inventory Provision'[ReportDate]) VAR StartDate = EOMONTH(CurrentDate, -12) + 1 VAR mthstart = MONTH(StartDate) var result = CALCULATE( SUM('Inventory Provision'[GrossDollars]), REMOVEFILTERS('Calendar'), 'Calendar'[Date]>= StartDate, 'Calendar'[Date] <= CurrentDate) RETURN DIVIDE(result,12) The goal is to get the total of the last 12 months for each report date i.e. if I filter the report to May 2024, I should see the 12 month average for the last 12 months from that current date. If I filter to June 2024, then last 12 months from that current date and so on and so forth. My report is filtered to May 2024, and I should see $4,346,951 in the below visual where it is highlighted.DAX Query
Hello Experts, I want to calculate average of a measure NR: NR=VAR CurrentDate = MAX('Dim - Calendar'[Fiscal Year ID]) VAR result = CALCULATE( COUNTROWS('Audit'), FILTER( ALLEXCEPT('Audit', 'Audit'[NAME]), 'Audit'[RATING] = "Needs Refinement" && 'Audit'[FISCAL_YEAR_ID] = CurrentDate ), CROSSFILTER('Dim - Calendar'[Date], 'Audit'[PLANNED_START], None) ) VAR max_notblank = CALCULATE( MAX('Audit'[FISCAL_YEAR_ID]), FILTER( ALLSELECTED('Audit'), 'Audit'[FISCAL_YEAR_ID] < CurrentDate || 'Audit'[FISCAL_YEAR_ID] <> BLANK() ), CROSSFILTER('Dim - Calendar'[Date], 'Audit'[PLANNED_START], None) ) VAR result1 = CALCULATE( COUNTROWS('Audit'), FILTER( ALLEXCEPT('Audit', 'Audit'[NAME]), 'Audit'[RATING] = "Needs Refinement" && 'Audit'[FISCAL_YEAR_ID] = max_notblank) ), CROSSFILTER('Dim - Calendar'[Date], 'Audit'[ENGAGEMENT_PLANNED_START], None) ) if(result = blank(),result1,result) for this I have written averagex(values(Audit[Name]),[NR]), here it is getting only result value, eeven I have data for previous year for other names. here for the selected year we only have data for one name, but as per my measureNR, we have 5 records as it is checking previous year too.now I want average of NRlike(6+4+5+10+11)/5=7.2. As averagex is not working I tried to create two different measures like(sumof NR/countof NR),I succeded in creating sum of NR measure but not count of NR please help
Avg of hourly sales
Hello all, I have a raw data with count figures (number of sales per day per hour) I used the sum function to get the total of each date per hour. (I'm also using a slicer to filter certain dates/hours) to compare the peak hours amoung diffrent timing/dates However, I'm not sure how to get the average of the sales (when i use the average measure ) it only gives the average of certain timings e.g. most of my sales per hour is 2-3, so the average is 2.5. However the actual average depends on the sum of the total sales. below is a sample of my data. Date hour Sales No 1-1-2023 0 2 1-1-2023 1 1 1-1-2023 2 3 1-1-2023 3 3 1-1-2023 4 2 1-1-2023 5 3 1-1-2023 6 2 2-1-2023 0 3 2-1-2023 1 2
