Previous week sales out of weekly data table

[akfir]

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

[FreemanZ]

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:

[akfir]

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.

[FreemanZ]

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:

[akfir]

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.

[FreemanZ]

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

[akfir]

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)

[FreemanZ]

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

[akfir]

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

[lbendlin]

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.

[akfir]

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