Multiple condition & Power Formula in Power BI issue

[JIN23]

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

[Anonymous · Accepted solution]

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]

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