Need Dynamic Forecast Measure

[ayeoh]

URGENT DAX Question:

How do I modify this dax: Forecast = CALCULATE(SUM('Q3 Forecast'[Forecast]))
such that selected value if dimTime[Fiscal Quarter]=Q2, then CALCULATE(SUM('Q2 Forecast'[Forecast]),

otherwise if selected value of dimTime[Fiscal Quarter] = Q3 or Q4, then CALCULATE(SUM('Q3 Forecast'[Forecast])).

Thanks so much!

[DataZoe · Accepted solution]

Hi @ayeoh ,

If it's only Q2 that has a different forecast, you might be able to try this simple solution:

SWITCH(
SELECTEDVALUE('dimTime'[Fiscal Quarter]),
"Q2", SUM('Q2 Forecast'[Forecast]),
SUM('Q3 Forecast'[Forecast])
)

 It could be an IF too, but I left it at SWITCH in case you wanted to check for BLANK() and have it return BLANK() if no quarter is selected.

[ayeoh]

Thanks @DataZoe That worked!

[aj1973]

Hi @ayeoh 

Try this

Forecast =
VAR _FiscalQurter = SELECTEDVALUE(dimTime[Fiscal Quarter])

VAR _F1 = CALCULATE(SUM('Q1 Forecast'[Forecast]))

VAR _F2 = CALCULATE(SUM('Q2 Forecast'[Forecast]))

VAR _F3_F4 = CALCULATE(SUM('Q3 Forecast'[Forecast]))

VAR Result = IF(_FiscalQurter = Q1, _F1 ,
                                    IF(_FiscalQurter = Q2,

                                                                       _F3_F4
                         ) )
RETURN

Result

[ayeoh]

Thanks Amine. Can you pls take a look at the screenshot.

I need it so if Fiscal Quarter = Q2, then _F2, but if Fiscal Quarter = Q3 or Fiscal Quarter = Q4, then _F3_F4.

The 1st condition is clear to me (Q2).

But how can I modify the 2nd condition so that if Fiscal Quarter = Q3 or Q4, then _F3_F4.

If I want to use a SWITCH statement, how would that look like?
Thanks!

[aj1973]

How about Q1, Return = Blank()? if so then

VAR Result = IF(_FiscalQurter = Q2, _F2 ,
                                    IF(OR(_FiscalQurter = Q3, _FiscalQurter = Q4)

                                                                       _F3_F4,

                                                                                Blank()
                         ) )

[ayeoh]

Thanks again Amine. I don't need Q1 because there is not forecast for Q1 (Q1 is only budget so pulls from different table). So I modified the formula and there is new error 😞

[aj1973]

check out which one!

[ayeoh]

Do you mean delete each ")" by trial and error until the error is resolved?