Re: WEEKLY TRENDS comparison

jayceeb

Hi, I have daily data entry I just want to get the weekly trends comparison example my data is

Date	Quantity
1-Jan	1
2-Jan	1
3-Jan	1
4-Jan	1
5-Jan	1
6-Jan	1
7-Jan	1
8-Jan	1
9-Jan	1
10-Jan	1
11-Jan	1
12-Jan	1
13-Jan	1
14-Jan	1
1-Feb	2
2-Feb	2
3-Feb	2
4-Feb	2
5-Feb	2
6-Feb	2
7-Feb	2
8-Feb	2
9-Feb	2
10-Feb	2
11-Feb	2
12-Feb	2
13-Feb	2
14-Feb	2

I want my result to show like this if i select the date filter . is it possible to put it in graph as well? please help. Thank you:)

Month	Week	Total
Jan 1-Jan 7	week 1	7
Feb 1- Feb 7	week 1	14
Jan 8-Jan 14	week 2	7
Feb 8- Feb 14	week 2	14

v-yilong-msft

Hi @jayceeb,

I create a table as you mentioned.

Then I create two calculated columns and here are the DAX codes.

WeekRange =

VAR StartOfWeek =

'Table'[Date] - WEEKDAY ( 'Table'[Date], 2 ) + 1

VAR EndOfWeek = StartOfWeek + 6

RETURN

FORMAT ( StartOfWeek, "MMM d" ) & " - "

& FORMAT ( EndOfWeek, "MMM d" )

WeekNumber =

"week "

& WEEKNUM ( 'Table'[Date], 2 ) - WEEKNUM ( STARTOFMONTH ( 'Table'[Date] ), 2 ) + 1

Next I think you can create a new table.

WeeklyTrends =

SUMMARIZE(

'Table',

'Table'[WeekRange],

'Table'[WeekNumber],

"Total", SUM('Table'[Quantity])

)

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

jayceeb

hi i got it however the date is not arrange.

i use the sorting but still mixed. and in the table wek1 and week 6 is the same date. i also use filter for the dates. how to arrange the date please.