Forum Discussion

JIN23's avatar
JIN23
Icon for Helper I rankHelper I
2 years ago
Solved

Need a help with multiple condition & SQRT Formula

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...
  • bhanu_gautam's avatar
    bhanu_gautam
    2 years ago

    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()
    )
    )
    )

bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper User
2 years ago

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's avatar
JIN23
Icon for Helper I rankHelper I
2 years ago

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's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      JIN23
      Icon for Helper I rankHelper I
      2 years ago

      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