cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ErikPBI Frequent Visitor
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
ErikPBI Frequent Visitor
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
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...


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


ErikPBI Frequent Visitor
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 Smiley Happy

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


ErikPBI Frequent Visitor
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 :-)  

ErikPBI Frequent Visitor
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 :-)