cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Find the 3 previous weeks sales from selected week

Hi,

I have a formula today to find out previous week sales from selected week.

```Previous Week Sales =
VAR CurrentWeek = SELECTEDVALUE(Date_table[Week Nr])
VAR CurrentYear = SELECTEDVALUE(Date_table[Year])
VAR MaxWeekNumber = CALCULATE(MAX(Date_table[Week Nr]);ALL(Date_table))

// If week = 1, then it will calculate against week 52 or 53 fix
RETURN
SUMX(
FILTER(ALL(Date_table);
IF(CurrentWeek = 1;
Date_table[Week Nr] = MaxWeekNumber && Date_table[Year] = CurrentYear - 1;
Date_table[Week Nr] = CurrentWeek -1 && Date_table[Year] = CurrentYear) );
[Sum Revenue])```
This works great, but I have got a question that a user wants to see the last 3 weeks sales from selected week.  I have some difficulty to solve this, any suggestions?

I have a dedicated  time table with weeks, year, date and so on.
1 ACCEPTED SOLUTION

Accepted Solutions
Frequent Visitor

## Re: Find the 3 previous weeks sales from selected week

I tried that but got:

`The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression.`

However, I tried out something and ended up with this, that works:

```3 Previous QTY Week Sales =
VAR CurrentWeek = SELECTEDVALUE(Date_table[Week Nr])
VAR CurrentYear = SELECTEDVALUE(Date_table[Year])
VAR MaxWeekNumber = CALCULATE(MAX(Date_table[Week Nr]);ALL(Date_table))

// If week = 1, then it will calculate against week 52 or 53 fix
RETURN
SUMX(
FILTER(ALL(Date_table);
IF(CurrentWeek = 1;
Date_table[Week Nr] = MaxWeekNumber && Date_table[Year] = CurrentYear - 1;
Date_table[Week Nr] < CurrentWeek  && Date_table[Week Nr]>CurrentWeek -4 &&                        Date_table[Year] = CurrentYear) );
[Sum Revenue product])```

Thanks for your help, learned something new today :-)

5 REPLIES 5
Super User

## Re: Find the 3 previous weeks sales from selected week

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

Proud to be a Datanaut!

Frequent Visitor

## Re: Find the 3 previous weeks sales from selected week

Hi,

Thanks for reaching out!

I tried this:

```3 Previous Week Sales =
VAR MAXYEAR = MAX(Date_table[Year])
VAR MAXWEEK = MAX(Date_table[Week Nr])
VAR TMPTABLE = CALCULATETABLE(Date_table;ALL(Date_table[Year]);ALL(Date_table[Week Nr]))
RETURN
SUMX(FILTER(TMPTABLE;Date_table[Year] = MAXYEAR && Date_table[Week Nr] <= MAXWEEK); [Sum Revenue])```

This gave me YTD sales in week from selected week, but I'm trying to find out the 3 last weeks sales from selected week

Super User

## Re: Find the 3 previous weeks sales from selected week

I think you want:

```3 Previous Week Sales =
VAR MAXYEAR = MAX(Date_table[Year])
VAR MAXWEEK = MAX(Date_table[Week Nr])
VAR TMPTABLE = CALCULATETABLE(Date_table;Date_table[Year]=MAXYEAR && Date_table[Week Nr]<MAXWEEK && Date_table[Week Nr]>MAXWEEK-3))
RETURN
SUMX(TMPTABLE; [Sum Revenue])```

Something like that. Might have an issue with year roll-over.

Proud to be a Datanaut!

Frequent Visitor

## Re: Find the 3 previous weeks sales from selected week

I tried that but got:

`The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression.`

However, I tried out something and ended up with this, that works:

```3 Previous QTY Week Sales =
VAR CurrentWeek = SELECTEDVALUE(Date_table[Week Nr])
VAR CurrentYear = SELECTEDVALUE(Date_table[Year])
VAR MaxWeekNumber = CALCULATE(MAX(Date_table[Week Nr]);ALL(Date_table))

// If week = 1, then it will calculate against week 52 or 53 fix
RETURN
SUMX(
FILTER(ALL(Date_table);
IF(CurrentWeek = 1;
Date_table[Week Nr] = MaxWeekNumber && Date_table[Year] = CurrentYear - 1;
Date_table[Week Nr] < CurrentWeek  && Date_table[Week Nr]>CurrentWeek -4 &&                        Date_table[Year] = CurrentYear) );
[Sum Revenue product])```

Thanks for your help, learned something new today :-)

Frequent Visitor

## Re: Find the 3 previous weeks sales from selected week

I tried that but got:

`The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression.`

However, I tried out something and ended up with this, that works:

```3 Previous QTY Week Sales =
VAR CurrentWeek = SELECTEDVALUE(Date_table[Week Nr])
VAR CurrentYear = SELECTEDVALUE(Date_table[Year])
VAR MaxWeekNumber = CALCULATE(MAX(Date_table[Week Nr]);ALL(Date_table))

// If week = 1, then it will calculate against week 52 or 53 fix
RETURN
SUMX(
FILTER(ALL(Date_table);
IF(CurrentWeek = 1;
Date_table[Week Nr] = MaxWeekNumber && Date_table[Year] = CurrentYear - 1;
Date_table[Week Nr] < CurrentWeek  && Date_table[Week Nr]>CurrentWeek -4 &&                        Date_table[Year] = CurrentYear) );
[Sum Revenue product])```

Thanks for your help, learned something new today :-)