cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
rf Frequent Visitor
Frequent Visitor

Week to date this week compared to week to date prior Week

Hi everyone, 

I need to calculate the number of units sold this week to date in one measure and with a 2nd measure compare it to the prior week to date, such that units from Sunday through Friday of this week are compared with units from Sunday through Friday of last week. I've worked out units week to date this week below, also worked out units prior *full* week (Sunday through Monday) however *week to date" prior week (as opposed to full week) evades me. Thanks in advance for any ideas:

 

Measures:

 

Units WTD =
VAR CurrentDate = LASTDATE ( 'Date'[CalendarDate] )
VAR DayNumberOfWeek = WEEKDAY ( LASTDATE ( 'Date'[CalendarDate] ), 3 )

 

RETURN
CALCULATE (SUM ( Orders[Units] ),
DATESBETWEEN ('Date'[CalendarDate],DATEADD ( CurrentDate, -1 * DayNumberOfWeek, DAY ),CurrentDate))


Units Prior Full Week =
VAR CurrentWeek = SELECTEDVALUE('Date'[WeekNo])
VAR CurrentYear = SELECTEDVALUE('Date'[Year])
VAR MaxWeekNumber = CALCULATE(MAX('Date'[WeekNo]), ALL('Date'))

RETURN
SUMX(FILTER(ALL('DATE'),IF(CurrentWeek = 1,'Date'[WeekNo] = MaxWeekNumber && 'Date'[Year] = CurrentYear - 1,
'Date'[WeekNo] = CurrentWeek - 1 && 'Date'[Year] = CurrentYear )),
[Units]) 

 

Seems like it should be something like this:

 

Units WTD Prior Week =
VAR CurrentDate = LASTDATE('Date'[CalendarDate])
VAR DayNumberOfWeek = WEEKDAY(LASTDATE('Date'[CalendarDate]),3)
VAR CurrentWeek = SELECTEDVALUE('Date'[WeekNo])
VAR CurrentYear = SELECTEDVALUE('Date'[Year])
VAR MaxWeekNumber = CALCULATE(MAX('Date'[WeekNo]), ALL('Date'))

RETURN
SUMX(
FILTER(ALL('Date'), AND(
IF(CurrentWeek = 1,
'Date'[WeekNo] = MaxWeekNumber && 'Date'[Year] = CurrentYear - 1,
'Date'[WeekNo] = CurrentWeek - 1 && 'Date'[Year] = CurrentYear ), DATESBETWEEN('Date'[CalendarDate],DATEADD(CurrentDate,-1*DayNumberOfWeek,DAY),CurrentDate))),
[Units])

1 ACCEPTED SOLUTION

Accepted Solutions
rf Frequent Visitor
Frequent Visitor

Re: Week to date this week compared to week to date prior Week

I came up with the following solution:

 

Revenue WTD =
VAR CurrentDate=LASTDATE('Date'[CalendarDate])
VAR DayNumberOfWeek=WEEKDAY(LASTDATE('Date'[CalendarDate]),3)
RETURN
CALCULATE(SUM(Orders[total_price]),DATESBETWEEN('Date'[CalendarDate],DATEADD(CurrentDate, DayNumberOfWeek * -1, DAY),CurrentDate))

 

Revenue WTD Prior Week =
VAR CurrentDate=LASTDATE('Date'[CalendarDate])
VAR PriorWeekDate=DATEADD(CurrentDate,-7,DAY)
VAR DayNumberOfWeek=WEEKDAY(LASTDATE('Date'[CalendarDate]),3)
RETURN
CALCULATE(SUM(Orders[total_price]),DATESBETWEEN('Date'[CalendarDate],DATEADD(PriorWeekDate, DayNumberOfWeek * -1, DAY), PriorWeekDate))

View solution in original post

2 REPLIES 2
v-cherch-msft Super Contributor
Super Contributor

Re: Week to date this week compared to week to date prior Week

Hi @rf

 

Here are some references for you. If it is not your case, please share a simplified data sample and expected output.

https://community.powerbi.com/t5/Desktop/Last-Week-Previous-Week-Calculation-Formula-Review/td-p/218...

https://community.powerbi.com/t5/Desktop/Week-over-Week/td-p/379226

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
rf Frequent Visitor
Frequent Visitor

Re: Week to date this week compared to week to date prior Week

I came up with the following solution:

 

Revenue WTD =
VAR CurrentDate=LASTDATE('Date'[CalendarDate])
VAR DayNumberOfWeek=WEEKDAY(LASTDATE('Date'[CalendarDate]),3)
RETURN
CALCULATE(SUM(Orders[total_price]),DATESBETWEEN('Date'[CalendarDate],DATEADD(CurrentDate, DayNumberOfWeek * -1, DAY),CurrentDate))

 

Revenue WTD Prior Week =
VAR CurrentDate=LASTDATE('Date'[CalendarDate])
VAR PriorWeekDate=DATEADD(CurrentDate,-7,DAY)
VAR DayNumberOfWeek=WEEKDAY(LASTDATE('Date'[CalendarDate]),3)
RETURN
CALCULATE(SUM(Orders[total_price]),DATESBETWEEN('Date'[CalendarDate],DATEADD(PriorWeekDate, DayNumberOfWeek * -1, DAY), PriorWeekDate))

View solution in original post

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 455 members 4,634 guests
Please welcome our newest community members: