Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Need Dynamic Forecast Measure

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])...
Need Help
  • DataZoe's avatar
    DataZoe
    2 years ago

    Hi Anonymous ,

     

    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.

aj1973's avatar
aj1973
Community Champion
2 years ago

Hi Anonymous 

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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    aj1973
    Community Champion
    2 years ago

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

     

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

                                                                           _F3_F4,

                                                                                    Blank()
                             ) )

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        aj1973
        Community Champion
        2 years ago

         

        check out which one!