Forum Discussion
Get the Best Performing Month
Hi,
I am working on a measure that will return the best performing Week, and Month. The Best Performing Week or Month is simply the week or month with the highest Transaction Count.
i have search online but still unable to get this.
My Data is something like this:
| Transaction Date | Transaction_Count |
| 1/31/2020 | 138 |
| 2/1/2020 | 2416 |
| 2/2/2020 | 5776 |
| 2/3/2020 | 4679 |
| 2/4/2020 | 3763 |
| 2/5/2020 | 1173 |
| 2/6/2020 | 1311 |
| 2/7/2020 | 12 |
| 3/6/2020 | 2125 |
| 4/1/2020 | 4586 |
| 1/31/2020 | 3279 |
| 2/1/2020 | 251 |
| 2/2/2020 | 2339 |
| 2/3/2020 | 542 |
| 2/4/2020 | 13 |
| 2/5/2020 | 5758 |
| 2/6/2020 | 3783 |
| 2/7/2020 | 196 |
| 3/6/2020 | 537 |
| 4/1/2020 | 2141 |
| 1/31/2020 | 4939 |
| 2/1/2020 | 3925 |
| 2/2/2020 | 5520 |
Hi, Anonymous
According to your description, I think you can create a measure in card visual.
Like this:
Measure = VAR a = ADDCOLUMNS ( 'Table', "aa", SUMX ( FILTER ( ALL ( 'Table' ), MONTH ( [Transaction Date] ) = MONTH ( EARLIER ( 'Table'[Transaction Date] ) ) ), [Transaction_Count] ) ) VAR b = MAXX ( FILTER ( a, [aa] = MAX ( [aa] ) ), [Transaction Date] ) VAR c = INT ( DAY ( b ) / 7 + 1 ) RETURN "The Best Performing Week or Month is the " & c & " week of " & MONTH ( b )If it doesn’t solve your problem, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- amitchandak
Super User
Anonymous , Create a date table with Week and month and the create measure like
CALCULATE(max(Date[Week Number]),TOPN(1,all(Date[Week Number]),calculate(sum([Transaction_Count])),DESC),VALUES(Date[Week Number]))
CALCULATE(max(Date[Month Number]),TOPN(1,all(Date[Week Number]),calculate(sum([Transaction_Count])),DESC),VALUES(Date[Month Number]))
Week column in date table
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Number = WEEKNUM([Date],2)
Week = if('Date'[Week Number]<10,'Date'[Year]*10 & 'Date'[Week Number],'Date'[Year]&'Date'[Week Number])
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
Week name = [Week Start date] & " to "& [Week End date]To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
- v-janeyg-msft
Community Support
Hi, Anonymous
According to your description, I think you can create a measure in card visual.
Like this:
Measure = VAR a = ADDCOLUMNS ( 'Table', "aa", SUMX ( FILTER ( ALL ( 'Table' ), MONTH ( [Transaction Date] ) = MONTH ( EARLIER ( 'Table'[Transaction Date] ) ) ), [Transaction_Count] ) ) VAR b = MAXX ( FILTER ( a, [aa] = MAX ( [aa] ) ), [Transaction Date] ) VAR c = INT ( DAY ( b ) / 7 + 1 ) RETURN "The Best Performing Week or Month is the " & c & " week of " & MONTH ( b )If it doesn’t solve your problem, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
