Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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])
Solved! Go to 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))
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/Week-over-Week/td-p/379226
Regards,
Cherie
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))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |