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

[Anonymous]

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])

[v-cherch-msft]

Hi @Anonymous

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/218653

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

Regards,

Cherie

[Anonymous · Accepted solution]

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))