Get data from Previous week with all values.

[nutdiablo]

Dear All, Before I start, sorry for my English.
Here is my Data Source

And I want to create a DAX to get SUM of revenue of the prevoius week with all Pack, QouteNum, and Team.
Here is my DAX

PreviousWeekREV = CALCULATE (SUM(Sheet1[Revenue]),FILTER(ALL(Sheet1), Sheet1[WeekNumber] = SELECTEDVALUE(Sheet1[WeekNumber]) - 1),VALUES(Sheet1[Pack])).

BUT when I select week 32, it only show Pack B and C since in week 32 there is no Pack A, even I did select "Show Items With No Data".

Is there a way to do this and how?
Thank you. 

[rohit1991 · Accepted solution]

Hi @nutdiablo 

Could you please try the measures below:

Revenue = SUM ( Sheet1[Revenue] )

Previous Week Revenue =
VAR _prev = SELECTEDVALUE ( DimWeek[WeekNumber] ) - 1
RETURN
    CALCULATE ( [Revenue],
        REMOVEFILTERS ( DimWeek[WeekNumber] ),
        DimWeek[WeekNumber] = _prev
    )

Create formatting measures:

Revenue (0) := COALESCE ( [Revenue], 0 )

Prev Week (0) := COALESCE ( [Previous Week Revenue], 0 )

Data :

Outcome :

[nutdiablo]

It work! Thank you very much.