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.

[v-yilong-msft · Accepted solution]

Hi @jayceeb ,

In Power BI Desktop, if you want to use a DAX statement after selecting a date, you need to strictly follow the Week rule on the calendar to do so. 

WeeklyTotal = 
CALCULATE(
    [TotalQuantity],
    ALLEXCEPT('Table', 'Table'[MonthYear], 'Table'[WeekNumber])
)

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.

[amitchandak]

@jayceeb , You have create week columns in you table

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],1)+1 //Sunday

Week Start date Monday = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1 //Monday

Week End date = [Week Start date] +6

format and use in other column

You can check here

https://www.youtube.com/watch?v=cyWVzAQF9YU&t=40648s

Any Weekday Week - Start From Any day of Week
https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Powe...
https://medium.com/chandakamit/cheat-sheet-any-weekdays-week-start-date-just-one-variable-apart-6b2e...

Power BI — Week on Week and 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-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8