topic Re: Previous week sales out of weekly data table in DAX Commands and Tips

Previous week sales out of weekly data table

akfir — Fri, 20 Oct 2023 17:06:17 GMT

Hi,
i have this data table of WEEKLY sales (by week number):

i have a measure of Sales = sum(Quantity), i wish to create :
1. a measure of sales for the previous week (please notice the situation that the previous week for week1 is week52/53 of the previous year).
2. a measure of average sales UNTIL the previous week (exc. the current one)

thanks in advance,
Amit

Re: Previous week sales out of weekly data table

lbendlin — Sun, 22 Oct 2023 22:45:53 GMT

please notice the situation that the previous week for week1 is week52/53 of the previous year

You need better week numbers. I'd recommend you take the day number and integer divide it by 7. Then the "previous week" is exactly weeknumber-1, always.

Re: Previous week sales out of weekly data table

FreemanZ — Mon, 23 Oct 2023 03:46:19 GMT

hi akfir ,

Not sure if i fully get you. Inspired by Ibendlin's advice, i would suggest you try like:

1. supposing your sample data table looks like:

Date	Quantity
12/21/2022	100
12/22/2022	200
12/23/2022	300
12/29/2022	10
12/30/2022	20
12/31/2022	30
1/1/2023	3
1/2/2023	5

2. try to have a dates table like below and relate it with the data table.

Dates = ADDCOLUMNS( CALENDAR(MIN(data[Date]), MAX(data[Date])), "SerialWeekNumber", DATEDIFF(DATE(1900,1,1), [Date], WEEK) )

3. plot a table visual with Dates[Date] column and two measures like:

Sales of PW = CALCULATE( SUM(data[Quantity]), FILTER( ALL(dates), dates[SerialWeekNumber] = MAX(dates[SerialWeekNumber]) -1 ) )+0

History Week Average = VAR CurrentWeek = MAX(dates[SerialWeekNumber]) VAR StartWeek = MINX(ALL(Dates[SerialWeekNumber]), dates[SerialWeekNumber]) VAR HistoryTotal = CALCULATE( SUM(data[quantity]), FILTER( ALL(dates), dates[SerialWeekNumber] < CurrentWeek ) ) VAR HistoryWeeks =CurrentWeek - StartWeek VAR result = DIVIDE(HistoryTotal, HistoryWeeks) RETURN result

it worked like:

Re: Previous week sales out of weekly data table

FreemanZ — Mon, 23 Oct 2023 03:57:30 GMT

or if you prefer calculated columns (which is not advisible in most cases), try to add two calculated columns like:

Sales of PW = VAR _table = ADDCOLUMNS( ALL(data), "SerialWeekNumber", DATEDIFF(DATE(1900,1,1), [Date], WEEK) ) VAR CurrentWeek = DATEDIFF(DATE(1900,1,1), [Date], WEEK) VAR result = SUMX( FILTER( _table, [SerialWeekNumber] = CurrentWeek -1 ), data[Quantity] ) RETURN result

History Week Average = VAR _table = ADDCOLUMNS( ALL(data), "SerialWeekNumber", DATEDIFF(DATE(1900,1,1), [Date], WEEK) ) VAR CurrentWeek = DATEDIFF(DATE(1900,1,1), [Date], WEEK) VAR StartWeek = MINX(_table, [SerialWeekNumber]) VAR HistoryWeeks =CurrentWeek - StartWeek VAR HistoryTotal = SUMX( FILTER( _table, [SerialWeekNumber] < CurrentWeek ), data[quantity] ) VAR result = DIVIDE(HistoryTotal, HistoryWeeks) RETURN result

it worked like:

Re: Previous week sales out of weekly data table

akfir — Mon, 23 Oct 2023 05:46:45 GMT

thanks for your reply.
but there are different channels, different products etc. for each if i wish i would like to calculate those measures and thats why i need measures and not calculated columns.

Re: Previous week sales out of weekly data table

akfir — Mon, 23 Oct 2023 05:49:30 GMT

thanks for your reply.
but there are different channels, different products etc. for each if i wish i would like to calculate those measures and thats why i need measures and not calculated columns.
lets say i have a date column but it referrs to the first day of each week, as my data table is on a weekly basis with week numbers and not consecutive dates.

Re: Previous week sales out of weekly data table

akfir — Mon, 23 Oct 2023 05:51:33 GMT

thanks for your reply.
i didnt get it. could you please detail?

Re: Previous week sales out of weekly data table

FreemanZ — Mon, 23 Oct 2023 06:25:26 GMT

akfir try to provide more data to well reflect your situation.

Re: Previous week sales out of weekly data table

akfir — Mon, 23 Oct 2023 06:54:30 GMT

below is the matrix visual i need, with the measure result on the right. in addition as i mentioned originally, i need a measure calculates the history average (exc. current week sales)

Re: Previous week sales out of weekly data table

FreemanZ — Mon, 23 Oct 2023 08:16:32 GMT

hi akfir

could you post the corresponding raw data of your expected vasual?

Please also check this for data posting:

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Re: Previous week sales out of weekly data table

akfir — Mon, 23 Oct 2023 08:24:12 GMT

you can use the data from my original post. the numbers are a little different but it doesnt matter