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]),

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

Thanks so much!

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.

8 Replies

  • DataZoe's avatar
    DataZoe
    Microsoft Employee
    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()
                                 ) )