week average on missing values
Hi, I have a Table with Missing values on weekends and holidays, I need to populate missing values with the weekly average. I have used Simple Formula IF( ISBLANK(sales[KPMR]), AVERAGE(sales[KPMR]), sales[KPMR] ) but the average is for the whole period, not the row week I need. I have a similar dataset written down. date Weekday week num Values (KPMR) 10/11/2020 7 42 10/12/2020 1 42 11 10/13/2020 2 42 84 10/14/2020 3 42 42 10/15/2020 4 42 95 10/16/2020 5 42 42 10/17/2020 6 42 10/18/2020 7 43 10/19/2020 1 43 18 10/20/2020 2 43 24 10/21/2020 3 43 17 10/22/2020 4 43 47 10/23/2020 5 43 82 10/24/2020 6 43 10/25/2020 7 44 how can I Populate missing values based on the weekly average or monthly average? I have multiple rows per day (let's say transactions), but the Value (KPMR) will be the same.
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, HelenShow Week Data for the last 8 weeks and display the average of the 8-weekly data
Hello everyone, I need some help and I would really, really appreciate any inputs. Desired Output: Dynamically show the weekly data for the last 8 weeks (including current selected week) and then calculate 8 week average based on the current selected week. Example: If I select 3/12/2021 (Corresponds to Week 13), table will show the Sales on 3/21/21 and the weekly sales in the last 7 weeks. Sample Output: I have 2 tables Sales_Fact and dimWeek joined by Closing Week. What I did is create 8 different measures: Current Wk, Last_Wk1, Last_Wk2, Last_Wk3, Last_Wk4, Last_Wk5, Last_Wk6, Last_Wk7 Last_Wk1 = var selectedwk = max(dimWeek[Week_No]) var wk = calculate(selectedwk-1) return calculate([NB_PL_CY],all(dimWeek[Week_No]),filter(all(dimWeek),dimWeek[Week_No]=wk)) Then I created Avg_8_Wk measure that average all those 8 measures. This is the result: This shows the last 8 weeks Sales, however, I don't think this is the right way to do it and also I would like to be able to see the Week_Ending date that dynamically change instead of the measure name (Please see sample output above). Would you be able to help? Thanks so much in advance! Best, Newbie_2020
weekly average from period between 2 dates table
hello to everybody I have a table with 4 columns: member_Id | start date | end date| status and a day by day calendar in another sheet I can, thanks to v-jiascu-msft , https://community.powerbi.com/t5/Desktop/Count-distinct-between-two-date-with-date-slicer-for-line-chart/td-p/445710 count the number of people each day and slice by category or whatever. I need now to compute a weekly average like this : if a personn belongs to the table for only 3 days in a week, it will add only 3/7 for this particular week. Furthemore, it would be great to look after the working days on one side and the weekend on the other side, or the holidays, or any category in my calendar. I can't do it in a day-by-day table because it would lead to make a huge number of rows. thanks to everyone Phil
