Need a help with multiple condition & SQRT Formula

[JIN23]

Hi

I want to calculate Forecasted stock gross based on weeks and week diff and here is what I have wrote. Any one confirm if it is correct?

Forecast Stock Gross = IFERROR(calculate([Bought Gross],FILTER(Dim_WK,Dim_WK[week_full_no] >= Dim_WK[Launching week])) ||

CALCULATE(power([Closing week diff],[Bought Gross]*(1-[Speed Plan])),FILTER(Dim_WK,Dim_WK[week_full_no] > Dim_WK[Closing week])) ||

CALCULATE(POWER([Closing week diff],[Stock gross]*(1-[Speed Plan])),FILTER(Dim_WK,Dim_WK[week_full_no]=Dim_WK[Closing week])),"")

Basically, in excel

If (current week >= launching week, then sum bought gross,

if current week > closing week, then bought gross*(1-speed plan) ^ closing week diff,

If current week = closing week, then stock gross*(1-speed plan)^closing week diff, "")

There was no error with my dax but I see result is blank.... could you help me here?

Thanks

[bhanu_gautam · Accepted solution]

@JIN23 , Try using below mentioned measure

Forecast Stock Gross =
IF(
MAX(Dim_WK[week_full_no]) >= MAX(Dim_WK[Launching week]),
CALCULATE(SUM([Bought Gross]), FILTER(Dim_WK, Dim_WK[week_full_no] >= Dim_WK[Launching week])),
IF(
MAX(Dim_WK[week_full_no]) > MAX(Dim_WK[Closing week]),
CALCULATE(POWER([Closing week diff], [Bought Gross] * (1 - [Speed Plan])), FILTER(Dim_WK, Dim_WK[week_full_no] > Dim_WK[Closing week])),
IF(
MAX(Dim_WK[week_full_no]) = MAX(Dim_WK[Closing week]),
CALCULATE(POWER([Closing week diff], [Stock gross] * (1 - [Speed Plan])), FILTER(Dim_WK, Dim_WK[week_full_no] = Dim_WK[Closing week])),
BLANK()
)
)
)

[JIN23]

Thanks soo much. It is working. But just two questions!!!

1, Max function: What is the reason to put Max function to the week? is it the function to bring dimension to the condition? If I select several weeks, it does impact any other calculation? Just wondering if I select multiple weeks at filter, if it breaks the calculation result. 

2. Power: If I want 3^2 = 9  Power(2,3) is correct or Power(3,2) is correct?

Thanks

JIN

BR

[bhanu_gautam]

@JIN23 The MAX function is used to ensure that you are comparing the highest value of the week-related columns (week_full_no, Launching week, Closing week) within the current filter context. This is particularly useful when you have multiple weeks selected in your filter.

3^2 = 9 It is in this format 3 raised to power of 2

[JIN23]

Thanks!!

Sorry. I am very beginner!! hahaha

Since I need to aggregate up in the right level, not to only fetching max weeks when I select multiple weeks, is there any formula I can use instead of Max?

Like. If result was

W23 = 1000

W24 = 1100

Total is 2200

But with current Max the total result is only 1100.

Thanks