DAX - sales previous week based on filter

[jayjay0306]

Hi people,

I need some help here on what - at least appears to be - a simple task:

I need to make a DAX measure showing the sales last week and the week before, based on the weeknumber I choose in the "week"-filter in the report.

Now, I have tried to make the following Dax (as you can see I have an attribute ("CALENDAR_PREVIOUS_WEEK") in my calendar table showing previous week, which I am trying to use, but I am not sure I can use it due to the filter requirement):

Sale prev. week = CALCULATE(SUM('KPI Sell Out'[Sales Out Packs CY]),'Calendar'[CALENDAR_PREVIOUS_WEEK],filter('Calendar',YEAR('Calendar'[Date])=Year(TODAY())))

 But the  result is not correct. (below)

One issue is to "overrule" the filter showing one week, where we need to see the previous week (which is basically out of range of the week chosen in filtter).

If anyone have a solution, it will be greatly appreciated.

thanks.

Br,

JayJay0306

[v-yadongf-msft · Accepted solution]

Hi @jayjay0306 ,

This is my test table:

Calendar table:

Relationship:

Create a measure:

sales previous week =
VAR current_week =
    SELECTEDVALUE ( 'CalendarTable'[Current_Week] )
VAR previous_week = current_week - 1
RETURN
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER (
            ALL ( 'CalendarTable' ),
            'CalendarTable'[Current_Week] = previous_week
                && YEAR ( 'CalendarTable'[Date] ) = YEAR ( TODAY () )
        )
    )

I think you will get the result you want:

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.