Forum Discussion

nutdiablo's avatar
nutdiablo
Frequent Visitor
1 year ago
Solved

Get data from Previous week with all values.

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's avatar
    rohit1991
    1 year ago

    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 :

     

     

2 Replies

  • rohit1991's avatar
    rohit1991
    Icon for Super User rankSuper User
    1 year ago

    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 :