cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
amane
Frequent Visitor

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) 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
)
)
1 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:

v-kelly-msft_0-1623653828036.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

View solution in original post

2 REPLIES 2
amane
Frequent Visitor

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:

v-kelly-msft_0-1623653828036.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors