Forum Discussion

JIN23's avatar
JIN23
Helper I
2 years ago
Solved

Multiple condition & Power Formula in Power BI issue

Hi

 

I want to calculate Forecasted stock gross based on weeks and week diff and here is what I have wrote. 

 

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
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi JIN23 ,

     

    Thanks for reaching out to the community. Please try:

     

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

     

    This formula uses nested IF statements to handle the different conditions and SUM to aggregate the Bought Gross values. It also ensures that the POWER function is used correctly.

    Give this a try and see if it resolves the issue. If you still encounter problems, please let me know!

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi JIN23 ,

     

    Thanks for reaching out to the community. Please try:

     

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

     

    This formula uses nested IF statements to handle the different conditions and SUM to aggregate the Bought Gross values. It also ensures that the POWER function is used correctly.

    Give this a try and see if it resolves the issue. If you still encounter problems, please let me know!

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • JIN23's avatar
      JIN23
      Helper I
      2 years ago

      The things is that I can not bring Week dimension to the if condition... 

      Since those week diff/full week no / launching week / closing week are at different dim tables.

       

      Is there anyway I can bring those dimension to the if condition?

      Like this.. BI can not find all the other dim tables...

       

      Thanks