Latest X Number of Weeks Help

[AndrewUrban]

Hello!

I'm looking for a DAX formula for "Last X weeks in Units." I need this value to populate without having to select a specific week - similar to the "LastDate" function.

See below for the table I am trying to populate, I also am using a seperate standardized "Date" table. The sales value I am trying to calculate is titled "Units" pulling from a "Sales" fact table.

Matrix:

Thanks so much for your help!!

[Anonymous]

@AndrewUrban , it would be very helpful if you could post the DAX for your existing measure [Last Week's Units]. It should be easy to extend the logic in that measure to look back 4 weeks instead of just 1 week.

[AndrewUrban]

Thanks for responding, see below for my DAX formula for the latest week's sales:

[Anonymous]

@AndrewUrban , that is interesting. Are all of your Sales for the week are all recorded against the last date in the week? Does this mean that the Sales[Date] column only ever contains dates that are end-of-week dates?

If Sales[Date] does indeed only contain end-f-week dates, then try this measure:

Last 4 Week's Units = 
    VAR vWeekEndDate1 = LASTDATE('Sales'[Date])
    VAR vWeekEndDate2 = CALCULATE(LASTDATE('Sales'[Date]), ALL('Sales'[Date]), 'Sales'[Date] < vWeekEndDate1)
    VAR vWeekEndDate3 = CALCULATE(LASTDATE('Sales'[Date]), ALL('Sales'[Date]), 'Sales'[Date] < vWeekEndDate2)
    VAR vWeekEndDate4 = CALCULATE(LASTDATE('Sales'[Date]), ALL('Sales'[Date]), 'Sales'[Date] < vWeekEndDate3)

    RETURN CALCULATE(SUM('Sales'[Units], 'Sales'[Date] >= vWeekEndDate4)

[v-xiaotang]

HI @AndrewUrban 

Thanks for reaching out to us.

>> I'm looking for a DAX formula for "Last X weeks in Units." I need this value to populate without having to select a specific week - similar to the "LastDate" function.

>>  I am trying to calculate is titled "Units" pulling from a "Sales" fact table.

please try this measure,

Last4WeekSale = 
var _thisweek = CALCULATE(MAX('Date'[WeekYearNumber]),'Date'[Date]=TODAY())
var _last4week=_thisweek-4
var _start= CALCULATE(COUNT('Date'[Date]),FILTER(ALL('Date'),'Date'[WeekYearNumber]=_last4week))
var _end= CALCULATE(MAX('Date'[Date]),FILTER('Date','Date'[WeekYearNumber]=_thisweek-1))
return CALCULATE(SUM(Sales[Units]),FILTER(Sales,Sales[date]>= _start &&  Sales[date]<=_end))

Date = 
ADDCOLUMNS (
CALENDAR (DATE(2022,1,1), DATE(2022,9,8)),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthShortYear",FORMAT([Date], "mmm-YYYY"),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ),
"Week Number", WEEKNUM ( [Date] ),
"Week Number and Year", "W" & WEEKNUM ( [Date] ) & " " & YEAR ( [Date] ),
"WeekYearNumber", YEAR ( [Date] ) & 100 + WEEKNUM ( [Date] )
)

Date table

Sales table

result

Best Regards,

Community Support Team _Tang

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

[AndrewUrban]

Thanks for your response! 

As I update data every week I'd like a fomrula to calculate the latest 4 weeks of sales data (total number of units over the last 4 weeks), so I can then create trended metrics. The only formula i've been able to come up with necesitates a selection of a specific week in a slicer/filter which does not work. 

[Greg_Deckler]

@AndrewUrban Using something like Sequential can make this super simple. Sequential - Microsoft Power BI Community