Forum Discussion
Typical value based on time range and category
I've seen a number of posts similar to this but am too new to be able to translate to my specific problem. Sorry if old news.
I'm trying to get the typical value (prefer median but okay with mean) over a date range where a category matches that of the current row.
Specifically I have a table with dates, the date's day of week, and a value. I want to see the typical value within the last x weeks where the weekday matches.
| Date | DayOfWeek | Value |
| 19/03/2020 | 3 | 233 |
| 18/03/2020 | 2 | 663 |
| 17/03/2020 | 1 | 488 |
| 16/03/2020 | 0 | 520 |
| 15/03/2020 | 6 | 813 |
| 14/03/2020 | 5 | 849 |
| 13/03/2020 | 4 | 331 |
| 12/03/2020 | 3 | 414 |
| 11/03/2020 | 2 | 591 |
| 10/03/2020 | 1 | 330 |
| 09/03/2020 | 0 | 705 |
| 08/03/2020 | 6 | 249 |
| 07/03/2020 | 5 | 794 |
| 06/03/2020 | 4 | 512 |
| 05/03/2020 | 3 | 583 |
| 04/03/2020 | 2 | 952 |
| 03/03/2020 | 1 | 449 |
| 02/03/2020 | 0 | 765 |
| 01/03/2020 | 6 | 554 |
The data is highly variant based on day of week and trends over time. I want a rough prediction of the value for the next day based on these elements.
TIA
@DominicHobbs - Yes, you totally forgot about a EVERYTHING
Median Measure = VAR __x = 1 //number of weeks VAR __MaxDate = MAX('Table (18)'[Date]) //current date in context VAR __MinDate = __MaxDate - (7 * __x) - 1 VAR __Table = FILTER(ALL('Table (18)'),[Date]>=__MinDate && [Date]<=__MaxDate) RETURN MEDIANX(FILTER(__Table,WEEKDAY([Date])=WEEKDAY(__MaxDate)),[DayTotalTickets])
6 Replies
- Greg_Deckler
Community Champion
DominicHobbs - You could do this:
Median Measure = VAR __x = 5 //number of weeks VAR __MaxDate = MAX('Table'[Date]) //current date in context VAR __MinDate = __MaxDate - (7 * 5) - 1 VAR __Table = FILTER('Table',[Date]>=__MinDate && [Date]<=__MaxDate) RETURN MEDIANX(FILTER(__Table,WEEKDAY([Date])=WEEKDAY(__MaxDate)),[DayOfWeekValue])Also, you may find this interesting - https://community.powerbi.com/t5/Quick-Measures-Gallery/To-bleep-With-MEDIAN/td-p/1322755
- amitchandak
Super User
DominicHobbs , same weekday is 7 days behind
7 Day behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-7,Day))
7th Last Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-7))
7th Last Day = CALCULATE(sum('order'[Qty]), previousday(dateadd('Date'[Date],-6,Day)))refer
Power BI — Day Intelligence
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9Power BI — WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123Thanks both for your replies. I think the table formatting in my OP has caused some confusion. There are 3 columns, the second being the DayOfWeek (int 0 to 6).
amitchandak I will look at the reference you have sent however I'm not struggling to identify what I want to filter by - rather to effectively filter/aggregate the values based on it. If I'm missing a solution to that in your reply then apologies. As I say, I'm new to Power BI and just now exploring its syntax etc.
Greg_Deckler I tried your code with minor tweaks (table name added, amended value col name, changed hard coded 5 to variable). The output was that the Median Measure column simply held the same values as the value column. Quite prepared to accept I messed up here but unsure how I managed that with the changes I made
Median Measure = VAR __x = 5 //number of weeks VAR __MaxDate = MAX(DailyTicketCount[Date]) //current date in context VAR __MinDate = __MaxDate - (7 * __x) - 1 VAR __Table = FILTER(DailyTicketCount,[Date]>=__MinDate && [Date]<=__MaxDate) RETURN MEDIANX(FILTER(__Table,WEEKDAY([Date])=WEEKDAY(__MaxDate)),[DayTotalTickets])Table below a mock-up example only
Date DayOfWeek DayTotalTickets Median Measure 19/03/2020 3 935 935 18/03/2020 2 259 259 17/03/2020 1 728 728 16/03/2020 0 239 239 15/03/2020 6 158 158 14/03/2020 5 876 876 13/03/2020 4 655 655 12/03/2020 3 226 226 11/03/2020 2 530 530 10/03/2020 1 485 485 09/03/2020 0 557 557 08/03/2020 6 359 359 07/03/2020 5 287 287 06/03/2020 4 255 255 05/03/2020 3 923 923 04/03/2020 2 482 482 03/03/2020 1 503 503 02/03/2020 0 879 879 01/03/2020 6 146 146 - Greg_Deckler
@DominicHobbs - Yes, you totally forgot about a EVERYTHING
Median Measure = VAR __x = 1 //number of weeks VAR __MaxDate = MAX('Table (18)'[Date]) //current date in context VAR __MinDate = __MaxDate - (7 * __x) - 1 VAR __Table = FILTER(ALL('Table (18)'),[Date]>=__MinDate && [Date]<=__MaxDate) RETURN MEDIANX(FILTER(__Table,WEEKDAY([Date])=WEEKDAY(__MaxDate)),[DayTotalTickets])
