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

4 Replies

  • 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