Forum Discussion

amane's avatar
amane
Frequent Visitor
5 years ago
Solved

Power BI Desktop - WoW Formula Issue

Hello Community,   I came up with below 4 formulas for the WoW calculation but I'm not able to calculate the value for the previous week! I have context filters applied! The DAX 4 returns (blank) ...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi amane ,

     

    You have to add "ALL" after filter function,since they are measures.

    See below:

    W Test 1 = CALCULATE(MAX(Sheet1[Week Rank]) , FILTER(ALL(Sheet1) , Sheet1[Start_Date] < Sheet1[current_date]))
    W Test 2 = CALCULATE(MAX(Sheet1[Week Rank]) , FILTER(ALL(Sheet1 ), Sheet1[Start_Date] < Sheet1[current_date])) - 1
    W Test 3 = 
VAR WW = [W Test 1]
RETURN
CALCULATE(
            SUM(Sheet1[Value]),
                FILTER(ALL(Sheet1) , Sheet1[Week Rank] = WW
            )    
    )
    W Test 4 = 
VAR WW = [W Test 2]
RETURN
CALCULATE(
            SUM(Sheet1[Value]),
                FILTER(Sheet1 , Sheet1[Week Rank] = WW
            )    
    )

    And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

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

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    5 years ago

    Hi amane ,

     

    You have to add "ALL" after filter function,since they are measures.

    See below:

    W Test 1 = CALCULATE(MAX(Sheet1[Week Rank]) , FILTER(ALL(Sheet1) , Sheet1[Start_Date] < Sheet1[current_date]))
    W Test 2 = CALCULATE(MAX(Sheet1[Week Rank]) , FILTER(ALL(Sheet1 ), Sheet1[Start_Date] < Sheet1[current_date])) - 1
    W Test 3 = 
VAR WW = [W Test 1]
RETURN
CALCULATE(
            SUM(Sheet1[Value]),
                FILTER(ALL(Sheet1) , Sheet1[Week Rank] = WW
            )    
    )
    W Test 4 = 
VAR WW = [W Test 2]
RETURN
CALCULATE(
            SUM(Sheet1[Value]),
                FILTER(Sheet1 , Sheet1[Week Rank] = WW
            )    
    )

    And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

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