Forum Discussion

Middleb's avatar
Middleb
New Member
2 years ago

More Complex Than I Thought - YTD on a Switch Expression

I'm having an issue with a YTD expression on top of a another esperssion that's working fine. The intent is to get an outlook for a metric (Sales for example). The underlying OUTLOOK expression is working fine, but I can't get a seemingly easy YTD expression to work properly embedding that OUTLOOK into the YTD DAX. Here's my code and results. Hopefully someone can help.

Outlook =
Var FCST_March = Calculate(SUM(CF[Fact-Forecast]), 'CF'[Version Type] = "March")
Var FCST_June = Calculate(SUM(CF[Fact-Forecast]), 'CF'[Version Type] = "June")
Var FCST_September = Calculate(SUM(CF[Fact-Forecast]), 'CF'[Version Type] = "September")
 
Return
switch(True,
    [CY Actual] <> 0, [CY Actual],
    FCST_September <> 0, FCST_September,
    FCST_June <> 0 , FCST_June,
    FCST_March <> 0, FCST_March,
    [Budget])
 
----------------------------
 
 Outlook YTD = TOTALYTD ( [outlook], 'CF'[Date].[Date] )  (I've tried just [date] as well)
 
Here is the results - as you can see the OUTLOOK formula is working by picking up one of the forcast or budget scenerios. But the YTD formula can seem to resolve this. Maybe it needs to be stored in a temp table for later use. My expected results are also shown on the far right.
 

 

Thanks for the help

Middleb

YTD Switch

2 Replies

  • DataNinja777's avatar
    DataNinja777
    Icon for Super User rankSuper User
    2 years ago

    Hi Middleb,

    If you are forecasting only once a month with Actual YTD of the previous month closed numbers plus forecast or budget for the YTG (year to go) period, it seems like you do not need to write the dax formula separating out March, June, and September in your code.  As it is only half of September, the actual number in September is just half the value of the other months, but do you need to substitute that with forecast or budget numbers?  I am assuming that you have a separate fact table for forecast or budget as well as actual fact table.  You can create relationships with your calendar dimension table with those fact tables and do a calculation like below to get the combined forecast YTG and YTD actual numbers.  

     

    • Middleb's avatar
      Middleb
      New Member
      2 years ago

      Sakiko - They do 1 budget and 3 quarterly forcasts. So mid quarter it would pick up any any actual closed months or the last quarterly forecast for months with no actuals. And they want to go back and see what those older forecasts were - so it's not just a rolling forecast. (unfortunatly).