Power BI Desktop - WoW Formula Issue

[amane]

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) value but the DAX 3 (current week value) returns the correct value

Dax 1 (Current Week Rank) : 

Week_Rank_Current = CALCULATE(MAX('CC Express'[Fiscal Week Rank]) , FILTER('CC Express' , 'CC Express'[fiscal_wk_starting_date] < 'CC Express'[Current_Date]))

Dax 2 (Previous Week Rank):

Week_Rank_Previous = CALCULATE(MAX('CC Express'[Fiscal Week Rank]) , FILTER('CC Express' , 'CC Express'[fiscal_wk_starting_date] < 'CC Express'[Current_Date])) - 1

Dax 3 (Current Week Revenue):

WoW Current Week Value =

VAR WW = [Week_Rank_Current]

RETURN

CALCULATE(

[Trial Usage Rate],

FILTER('CC Express' , 'CC Express'[Fiscal Week Rank] = WW

)

)

DAX 4 : Previous Week Revenue : 

WoW Previous Week Value =

VAR WW2 = [Week_Rank_Previous]

RETURN

CALCULATE(

[Trial Usage Rate],

FILTER(('CC Express') , 'CC Express'[Fiscal Week Rank] = WW2

)

)

[amane]

Here is the attached file : https://drive.google.com/file/d/1MmgEq7wchWK-Gj9dne4pf3hhJRBmceyW/view?usp=sharing

[v-kelly-msft · Accepted solution]

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!