Find the 3 previous weeks sales from selected week

[Anonymous]

 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. 

[Greg_Deckler]

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

[Anonymous]

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 🙂

[Greg_Deckler]

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.

[Anonymous · Accepted solution]

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 🙂  

[Anonymous]

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 🙂