Maintain Forecast DAX Calculation in Different Context

Need a little point to be able to maintain my forecast values in a different context.

I’ve created the following DAX Measure to take the previous quarters values in order to get an average for forecasting Customer renewals.

The works fine in the Table, as I need to maintain Customer as a Part of the Context to be able to calculate a forecast revenue for the Dates in the Context. I’m looking to also include the forecast in a stacked bar chart, but as the customer in not used, there will be no forecast as [Revenue] will not return Blank()

Forecast = 
IF(ISBLANK( [Revenue]), 
AVERAGEX( PARALLELPERIOD('Date'[Date],-1,QUARTER),
    [Revenue]),BLANK())

I believe I need to create a virtual tables using VALUES, build with Customer, Date, Revenue, but not clear on how best to go about this

I’ve attached a draft pbix file which demonstrates what I’m trying to do. I did scan through the resource on EnterprisedDNA, but I could not find anything that would help on what I’m trying to do.

Thank you
Nev

EnterpriseDNA_Help20200116.pbix (99.1 KB)

Hi, Neville. I am not sure if I understood your case well, but maybe you can just calculate forecast as Forcast= CALCULATE([Revenue]; PARALLELPERIOD(‘Date’[Date];-1;QUARTER)) and add it to the stacked bar chart.

.
Let me know if it helps.

Hi Ivanka,

Thank you, but this is not what I’m looking for, as I only require to include Customers that revenue is expiring as per the table of the left. What you’ve suggested does not take note of this as the CONTEXT is not including Customer in the Stack Bar Chart. This is the problem I’m facing.

I only what to forecast revenue for Customer that have BLANK for the dates in the CONTEXT (FYQ).

Nev

is this possible? I think I need to create a virtual table so I can include the forecast calculation in other visuals, without having the customer included in the context.

@Neville,

I know you asked @sam.mckay, but I hacked away at this for a long while last night so I hope you don’t mind me adding my $0.02. I am certain what you are trying to do is possible. When I first looked at it, it seemed pretty straightforward: create a virtual table with SUMMARIZE to provide the necessary context, add the forecast measure to the virtual table with ADDCOLUMNS, and then use SUMX to total the virtual forecast column. No problem, right? Well… then I realized what I’m sure you’ve already wrestled with at length - that having your measure triggered off a condition of BLANK() makes this fiendishly difficult and throws a big wrench into the above strategy, since SUMMARIZE, SUMMARIZECOLUMNS, and SELECTCOLUMNS all keep losing the blank rows, and thus the forecast measure never triggers.

I did come up with a virtual table construction approach that keeps the blank rows intact:

NATURALLEFTOUTERJOIN(
        CROSSJOIN(
            VALUES( 'Date'[MonthYearLong] ),
            VALUES( Revenue[Customer] )
        ),
        Revenue
    )

You can then wrap this in in ADDCOLUMNS function to add the forecast variable in context. I was able to do that successfully last night, although the forecast variable construct I used did not calculate properly, it did show up in the column and I was able to manipulate it via SUMX. In the current partial solution attached, where I tried (unsuccessfully by the time I finally had to call it a night) to fix the forecast calculation it is not showing up, but the earlier version definitely proved to me that this is possible.

See if this provides a useful kickstart for you. One suggestion - at least for me, the virtual table measure (vForecast) is far too complex to work on directly. Instead, I materialized it into an actual table (TestTable) and worked the code there so I could see what was going on, and then copied that back to the measure occasionally, just changing the final RETURN variable from a table to a scalar when I copied it back.

Hope this is helpful, even though not a full solution. Hopefully, you can crack it from here. If not, toss it back to the forum and we’ll give it another go. My partial solution file posted below.

P.S. I also played around with changing the blanks in [Total Revenue ] to zeros, which makes this whole problem easier but fundamentally changes the requirement. However, if it meets your needs to trigger the forecast metric off of [Total Revenue] = 0 rather than [Total Revenue] = BLANK, then the initial strategy laid out at the top of this post will work, because there will be no blank rows to lose.

@BrianJ, thank you for this. The issue with taken the value a 0, is that it quite possible that in the middle of some of the customer revenue period, there would be 0 revenue and I would not want to calculate forecast if there is revenue in the future against the same customer, only when its blank() is there no revenue.

How about we include the Project End date into the measure, so if MAX End Date (as there could be multiple projects for the customer) and [Total Revenue] is 0 then Forecast…Thoughts?

I really appreciate the time an effort on this, as I knew this was possible in DAX, just could not get my head around it, and reverted to a junior resource creating this in an XLS to import into the BI until I have a workable solution for forecasting.

Many thanks
Nev

1 Like

@Neville,

Thanks – that’s what I figured. You’ve clearly already given this a lot of work and thought, so if blank simply could have been replaced by zero you would’ve done it. I do like your thought of incorporating end date into the measure. I also had some ideas about creating a supplementary physical forecast table via DAX and linking that to our virtual table via LOOKUPVALUE(). I’ll keep hammering at this over the weekend and see if I can come up with a working full solution.

Tough, but really interesting problem that I hadn’t seen before. Good stuff…

  • Brian

@BrianJ,

Much appreciate,

You clearly love and challenge and not one to be deviated. Once we have a working solution, I’m sure it will be gratefully received by the Forum, and maybe @sam.mckay could include in one of his excellent showcases.

Any joy, as reverting to excel to get this done right now.

Thanks
Nev

@Neville,

Partial joy. :neutral_face:

I’ve been working this hard over the weekend, and have a general structure that I think will work, but I’m having a very difficult time getting your forecast measure to calculate properly in context within that structure. I will either crack this by the end of tonight, or if not, post my progress to the forum and see if others can punch it over the goal line.

This one has everything though - context transition, time intelligence, virtual relationships to supporting virtual and physical tables, etc. Really interesting and challenging. Will report back one way or the other tonight…

  • Brian

@Neville,

I went a full 12 rounds with this beast, and at the end of the fight I’m still not sure who came out on top. I definitely think I have a framework that does what you need it to do, but I’m not sure if the forecast numbers calculated are correct. This is where your Excel calculation will be helpful to validate one way or the other.

OK, so here’s the explanation of the attached solution:

  1. created a physical table via DAX, using the construct we discussed above in this thread - the only one of many I tried that retained the blank rows, which was necessary to trigger your forecast logic:

// The NATURALLEFTOUTERJOIN/CROSSJOIN construct is used to retain the
// blank rows necessary to trigger the forecast logic

FCast Lookup = 

    ADDCOLUMNS(    
    NATURALLEFTOUTERJOIN(
        CROSSJOIN(
            VALUES( 'Date'[MonthYearLong] ),
            VALUES( Revenue[Customer] )
        ),
        Revenue
    ),
    "vTotRevenue", CALCULATE(SUM( Revenue[Revenue] ))
)
  1. Added the forecast column to the above table. The TREATAS statement was used to create a virtual relationship between this table and the Date table, necessary for the time intelligence functions to operate properly. The ALLEXCEPT statement was used to remove the filters from MonthYearLong and Customer, in order to aggregate by the proper groupings:

// Outer CALCULATE statement is necessary for context transition

//  TREATAS is necessary to create the virtual relationship with the 
//  Date table required for time intelligence

FCast =

 CALCULATE(
    IF ( 
        SELECTEDVALUE( 'FCast Lookup'[vTotRevenue] )  = BLANK(),
        CALCULATE(
            AVERAGE( 'FCast Lookup'[vTotRevenue] ),
            PARALLELPERIOD ( 'Date'[Date], -1, QUARTER ),
           // DATEADD( 'Date'[Date], -1, QUARTER ),
            ALLEXCEPT(
                'FCast Lookup',
                'FCast Lookup'[MonthYearLong],
                'FCast Lookup'[Customer]
            ),
            ALLEXCEPT(
                'Date',
                'Date'[FiscalYearQuarter]
            ),
            TREATAS(
                VALUES( 'Date'[MonthYearLong] ),
                'FCast Lookup'[MonthYearLong]
            )
        ),
    BLANK ()
    )
)
  1. Added a column for Fiscal Year Quarter to provide the proper aggregation context for the table and column chart visuals:

FYQ =

CALCULATE(
    LOOKUPVALUE(
        'Date'[FiscalYearQuarter],
        'Date'[MonthYearLong],
        SELECTEDVALUE( 'FCast Lookup'[MonthYearLong] )
    )
)
  1. Created a measure to sum the forecast (FCast) column by FYQ, also requiring TREATAS to create the virtual relationship between the FCast and Date tables:

Total FCast by FYQ =

CALCULATE(
    SUMX(
        'FCast Lookup',
        'FCast Lookup'[FCast]
    ),
    ALLEXCEPT(
        'Date',
        'Date'[FiscalYearQuarter]
    ),
    TREATAS(
        VALUES( 'Date'[MonthYearLong] ),
        'FCast Lookup'[MonthYearLong]
    )
)

So, here’s what it looks like all put together:

As you can see in the screenshot above, I also created a measure version of the FCast calculated column, just to check whether it was calculating properly in context, since calculated columns can get wonky when you start adding in complex filter context changes. But in this case, both the calculated column and measure provide the exact same results line by line (except I didn’t bother to fix the grand total problem for the measure, since we know why that occurs and it’s not at all unique to this case).

Full solution file posted below. Hope this is helpful.

1 Like

@BrianJ, please share the pbix once Sam and team has address the issue. I understand the solution, but more of a practical man, so will wait for the file so I can review the whole solution.

Thanks
Nev

@Neville,

Forum is working again in terms of allowing uploads, so I updated the post from last night with the screenshot, the PBIX solution file and some additional explanation.

  • Brian

Thank you @BrianJ , this is of immense help. I’ve learnt a view things also.

One small change, I’m forecasting forward based on current quarter as of Today and only looking back to get the values to forecast, so what would need to change to achieve this?

CALCULATE(
    SUMX(
        'FCast Lookup',
        'FCast Lookup'[FCast]
    ),
    ALLEXCEPT(
        'Date',
        'Date'[FiscalYearQuarter]
    ),
    TREATAS(
        VALUES( 'Date'[MonthYearLong] ),
        'FCast Lookup'[MonthYearLong]
    )
, 'Date'[CurQuarterOffset]>=2)

Kind regards
Neville

@Neville,

Great- glad to hear that was helpful.

Do you have a mock-up of the results that you’re trying achieve (perhaps from your Excel analysis) that I can validate the results of this new question against?

Thanks.

  • Brian