DAX Formula for Weekly Average, to work with Date Range slicer
Hello Everyone, I was hoping you'd be able to help me with getting a DAX formula together. A bit of context - I have a dataset with a date table, and a table which is a list of 'Tickets issued'. They are linked via Issued Date. I am trying to get the average number of of tickets Issued a week, for each location (tickets have the location that they were issued to). I currently have the following DAX, but it isn't calculating properly (e.g. giving a greater average for a location with a lower number of total issued - see snapshot below) and I think this due to the 'Week Number' resetting at the start of each year, when my data spans 3+ years: Average Issued = AVERAGEX(VALUES( 'V_Date Table'[WeekNum]),[Total Issued]) Date Table = V_Date Table = ADDCOLUMNS (CALENDAR (DATE(2017,1,1), DATE(2025,12,31)), "DateAsInteger", FORMAT ( [Date], "DDMMYYYY" ), "Year", YEAR ( [Date] ), "Monthnumber", FORMAT ( [Date], "MM" ), "YearMonthnumber", FORMAT ( [Date], "MM-YYYY" ), "YearMonthShort", FORMAT ( [Date], "mmm-YYYY" ), "MonthNameShort", FORMAT ( [Date], "mmm" ), "MonthNameLong", FORMAT ( [Date], "mmmm" ), "WeekNum",WEEKNUM([Date],2)) Total Issued: Total Issued = DISTINCTCOUNT(R_Issued[Reference]) [Reference] is just a unique reference code for each ticket. Snapshot of current DAX not working (for a date range of the last 6 months, e.g.): Does anyone have a way to calculate this, (or know where I am going wrong), so that it works with any date range? Any help will be really appreciated! 🙂 Many thanks, Helen
Average of a Measure for a period of time
I have a measure called "First30Days" that goes back for 30 days of the Daily Sales and summarizes 30 days of sales for each calendar day. (I know the dataset is not every calendar day, that is ok.) First30Days = CALCULATE(('SalesTable'[DailySales]),DATESINPERIOD('Date Dim'[Date],NEXTDAY('Date Dim'[Date])-30,30,DAY)) As the screenshot indicates, this works fine and as expected. What I can't seem to figure out is how to average the First30Days column over 30 calendar days. I should end up having a new measure that shows me the 30 day average of the First30Days. Based on the screenshot, i should get 647,547 in my new column on the last row. (And, I will get a result in each row, but I am only showing for that one day in this example.) Year Quarter Month First 30 Days AVERAGE OF First 30 Days 2020 Qtr 2 June 22 $492,785 2020 Qtr 2 June 23 $515,496 2020 Qtr 2 June 24 $563,194 2020 Qtr 2 June 25 $589,350 2020 Qtr 2 June 26 $581,721 2020 Qtr 2 June 29 $612,746 2020 Qtr 2 June 30 $647,252 2020 Qtr 3 July 1 $660,788 2020 Qtr 3 July 2 $669,226 2020 Qtr 3 July 3 $629,239 2020 Qtr 3 July 6 $649,151 2020 Qtr 3 July 7 $680,475 2020 Qtr 3 July 8 $678,310 2020 Qtr 3 July 9 $679,352 2020 Qtr 3 July 10 $671,994 2020 Qtr 3 July 13 $678,355 2020 Qtr 3 July 14 $718,676 2020 Qtr 3 July 15 $732,290 2020 Qtr 3 July 16 $733,909 2020 Qtr 3 July 17 $704,970 2020 Qtr 3 July 20 $709,198 647,547
Calculate Average of Distinct count values by summarizing columns(Need Measure or Column not table)
Hello Team, I need your suggestions in creating Average value for Distinct count of ID's after summarizing three columns in a table. ID Category Code Date Subject Type 1 A X 7/20/2020 ABC Sales 1 A Y 7/21/2020 ABC Sales 2 B Z 7/20/2020 DEF Purchase 3 B Z 7/22/2020 DEF order 4 B Z 7/23/2020 XYZ order What I need is to Create Calculated column/ measure which gets Average of distinct count of ID column after summarizing Category, code, date, subject columns. Please suggest me dax code for this, Thank you,DAX problem sum average
Hi, i have a issue about my dax measure. SUM( fac_compras[cantidad] ) * CALCULATE( [avgCosteUnidad]; SAMEPERIODLASTYEAR(dim_date[Date] ) ) all is right, but the total multiplie total quantity (costeTotalTarLY) by average cost (Avg C.Und LY) and i need a sum of total. The total was be around 550.000 but is 3.109.896 Thanks for allHow to combine SUMMARIZE table with the Original Table?
Please see attached screenshot. I have an OG table (left). I then can use a SUMMARIZE function to create the middle table, which shows the AvgNumHours. I would like to combine the results to my OG table. My goal is to get to the table on the right exactly as shown with the blank spaces too. Is there a function or expression I can use to get to that table? Thanks!Cohort Analysis: Average Churn Rate
Hello, I'm new to Power BI and got struck while performing cohort analysis on customer data and trying to calculate the average across same row from multiple columns. I couldn't find any solutions related to it. So to start, I have created a dummy table with generate series to use it in cohort analysis and finding customer's that are retained. Upon doing that I have following as an example: Customers that are joined in Jan 2021 have retention rate of 96.4% for a month after. I have calculated month over month churn rate. My goal is to get the average churn rate for customers joined in January 2021 based on their churn rates in months after. For example: Average churn rate for customers joined in Jan 2021 = 0.46 + 15.53 + 16.48 / 3 = 10.82 per month. In this way I can use it to calculate the lifetime of a customer based on the average churn rate for customers that have their first sales on specific month. Hope this is clear. Could someone please guide them how to proceed? Thanks a lot in advance. Kind regards, Karthik
