Difference between last 13 weeks of current year and last 13 weeks of previous year same day or week
dm-p /@lbendlin/@Greg_Deckler Any ideas please? Hi All, I have a peacular scenario where in I have to find the difference between the sales of last 13 weeks from now and last year's same period(which is 13 weeks from same day last year). In fact I have few scenarios like 52 week, 10 week, 4 week etc. I am using the following two methods but not able to acheive. Could not attach the report as being sensitive and also file being multimillion in size. Couldn't keep records that satifies the current years and last years 10 weeks of data and size comeup to 2 million. Please advise with any sample calculations. Option-1: As my report should show the fullweeks of data until last week, the below delta between these two is giving the me cutshort weeks(for example, its taking from today whihc is not a full week) Volume_13weekRollingAvg = var NumDays = 91 var RollingSum = CALCULATE( SUM(tblfactcombinesales_daily_agg[agg_salesquantity]), DATESINPERIOD(tblfactcombinesales_daily_agg[transactiondate],LASTDATE(tblfactcombinesales_daily_agg[transactiondate]),-NumDays,DAY) ) RETURN RollingSum/NumDays ************************************ Volume_PRIOR13weekRollingAvg = var NumDays = 91 var RollingSum = CALCULATE( SUM(tblfactcombinesales_daily_agg[agg_salesquantity]), DATESINPERIOD(tblfactcombinesales_daily_agg[transactiondate],LASTDATE(tblfactcombinesales_daily_agg[transactiondate])-364,-NumDays,DAY) ) RETURN RollingSum/NumDays Diff = Volume_13weekRollingAvg - Volume_PRIOR13weekRollingAvg *********************************************************************************************************************************************************** Option-2: Dynamic way of choosing the weeks, however I am not able to reproduce the same calculation for the last years 13 weeks Created a Yearweek number from date. Test_13week_Average_Volume = var Numweeks = 13 var CurrentYearWeek = SELECTEDVALUE(tblfactcombinesales_daily_agg[YearWeek]) var AvgLastNweekVolume = AVERAGEX( ADDCOLUMNS( TOPN( Numweeks, CALCULATETABLE( VALUES(tblfactcombinesales_daily_agg[YearWeek]), tblfactcombinesales_daily_agg[YearWeek] < CurrentYearWeek), tblfactcombinesales_daily_agg[YearWeek], DESC ), "tempvolumes", [volumes] ), [tempvolumes] ) return AvgLastNweekVolume I have transactiondate & weeknum is my table
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_2020weekly 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 PhilCurrent weekly averages as compared to years past weekly averages
Hello, I am working with a large, police-data set looking at searches. The data set spans over 5 years, and there are duplicate items (example: if two subjects were apart of one search, they will have the same item number). I am able to create a measure that counts those individual items, but what I am trying to do is create a line and stacked column chart with the column showing the week-to-date total of searches, and the line showing the average from the weeks from the previous years (example: it is week 6 of 2019, so the column may show 40 searches that have happened thus far, and the line will show the running average of week 6 according to the data from 2014-2018). I cannot use the individual count measure that I made in this DAX expression. AvgPerWeek = CALCULATE(AVERAGE('Stop and Search'[Week Mon-Sun]),FILTER(ALL('Stop and Search'), 'Stop and Search'[item number this is where I would have liked to use the measure for individual count] =MAX('Stop and Search'[item number this is where I would have liked to use the measure for individual count]))) Any help you could give would be awesome.
