YTD eqv. for future

Jawed · May 27, 2019, 6:18pm

Hi everyone,

I have two facts table, one for “Actuals” and one for “Budget”. Now, I want to create a “Reforecast” calculation which will take the YTD figures and adds the remainder of the year to it to create a full year Reforecast. So, as an example, we are now in May 2019. I would like to see the Reforecast total as actuals Jan - May 2019 PLUS budgeted June - December 2019.

As my budget is on a monthly basis and the figures differ month on month, I cannot use the total divided by 12 and instead want the exact numbers.

My question is, using the same example, how do I create a dynamic Dax which pulls the total of reamining months based on the selected month?

sam.mckay · May 27, 2019, 11:20pm

Can you provide a bit more detail here as depending on the data, model and context you want to show this in the answer could be wildly different.

Have you had a change to go through the many budgeting videos that are available. There’s plenty to learn in these that will answer this for you I believe.

Here’s my recommended ones to work through.

One thing is that budgeting isn’t that simple in Power BI. You need to solve this in the model first and then with your DAX formulas.

Check out these ones to learn more.

By using a combination of techniques showcased here you’ll have a good understanding of how to work this out.

This particular workshop here is also a great one to review as it shows from start to finish how to work through budgeting scenarios.

Jawed · May 28, 2019, 11:24am

OK. Here is a very simple and crude example. Let’s assume this is a 6 months budget.

Budgeted Value:
Jan 2019 Feb 2019 Mar 2019 Apr 2019 May 2019 Jun 2019
Values 100 100 100 100 100 100
Total Budget 600

Now, let’s assume my YTD actuals are:
Jan 2019 Feb 2019 Mar 2019
Values 80 95 75
Total YTD 250

As such, my Forecast would be:
Jan 2019 Feb 2019 Mar 2019 Apr 2019 May 2019 Jun 2019
Values 80 95 75 100 100 100

Total Reforecast 550

Now, I can create this in Totals (i.e. Actuals YTD + [ budget total - YTD budget] = Reforecast Total). However, when I want to show them by month, it won’t show me the Reforecast values by month as illustrated above.

What I want to do is to use Dax and say that depending on the month selected, bring the YTD actual AND add the remaining budgeted amounts, taking into account the date context. That is to say that the YTD values and the remainder of the budgeted amounts should be pulled using their respective date context.

I hope it makes sense.

sam.mckay · May 29, 2019, 2:20am

Going to need to see a working example in a pbix file.

I recommend jumping into this and starting, as dreaming up what to do without working on it inside Power BI is almost impossible. There are too many factors at play.

We can assist more if can see the whole scenario laid out, with the model you have, the initial/core DAX calculations and then also exactly how you want to showcase this is a report.

All this matters and is what’s required to assist with a solution.

Jawed · May 29, 2019, 8:55am

Sure. Here is a sample data. There is actual for LY (FY19) and one month actual for TY (FY20) as well as full budget for FY20. As such, what I would like in this scenario is to take the one month actual and 11 month budget to create a new Reforecast.

Sample.pbix (748.3 KB)

sam.mckay · May 31, 2019, 12:06am

I’ve just attempted to do a bit of setup here and created this table.

I believe this is what you mean…I think.

Model looks ok here, but recommend cleaning this up a little bit

Check out here to learn more about this.

So, if I’m understanding this correctly, you want to integrate actual and forecasts into a new measure, taking one month and then add on the other 11 months.

Somehow you need a TRUE/FALSE calculation which shows you whether for any month you have the full actuals amount.

I’ve just made one up here, while you want to make this dynamic I would say.

HaveFullActuals =
IF( MAX( Dates[Date] ) &lt; TODAY(), TRUE(), BLANK() )

Then you should be able to create a formula which brings them both together.

Reforecast = 
SUMX( VALUES( Dates[MonthInCalendar] ),
    IF( [HaveFullActuals] = TRUE(), [Total Actuals], [Total Budget] ) )

This to me does what you’ve explained so hopefully, this was the solution you are looking for.

Thanks
sam

Jawed · May 31, 2019, 8:50am

Thank you. It does do indeed. This model is my first attempt after having gone through the material including the one you just shared. As such, I am happy that it “looks ok” but, of course, I will see how best I can clear them up.

Jawed · June 2, 2019, 8:54pm

I used VAR and combined both Dax. Everything works except the total. Here is the DAX

Reforecast =

VAR

HaveActuals = IF( MAX( Dates[Date] ) &lt; today (), TRUE(), BLANK())

RETURN

SUMX( VALUES( Dates[MonthInCalendar] ),

    IF( HaveActuals = TRUE(), [Total Actuals], [Total Budget] ) )

and here is the result table:

Screenshot

sam.mckay · June 3, 2019, 11:07pm

Don’t use VAR here. You need to be doing all of this inside the row context of the SUMX function. If you complete it in a variable here you’ll find the same issue as last time in that the evaluation happens outside.

Give that a go.

Sam

Jawed · June 4, 2019, 4:27pm

Thank you. Broke them into two and all looks fine now.

Jawed · July 3, 2019, 1:00pm

Hi. I have now further developed my model and have come across another issue. My “Total” for the months that are coming from Actual, shows as zero. However, the total for the values that are coming from the budgeted/forecast, they are showing fine. Any help would be greatly appreciated.[Sample.pbix|attachment]

(upload://wcLag0mHlnPwxzhlpl8UndUryUG.pbix) (1.1 MB)

sam.mckay · July 3, 2019, 9:58pm

Can’t download the file for some reason. Maybe try adding it again. Thanks

Jawed · July 4, 2019, 8:35am

Here you go.

Sample.pbix (1.1 MB)

sam.mckay · July 7, 2019, 10:03pm

I’m confused here. Can you be more specific where the issue is here. I’m looking at your demo model and it doesn’t really match up to your question at all??

Don’t even see actuals or forecasts?

Jawed · July 7, 2019, 11:06pm

Sorry for the confusion. This is in continuation of all the previous exchanges. To cut to the chase, if you look at the Apr and May Total, it is not correct. For example, 1.2m minus 372K doesnt equal to zero. Equally, in May, 270 minus 375 doesn’t equal to zero.

As to the “longer” story, the values are pulled out from actuals (April and May) and forecast (Jun - Mar) based on the useful DAX you had helped earlier.

sam.mckay · July 8, 2019, 5:00am

Ok sure thing.

To fix this then, the strategy is to just break things down into separate formulas so you know how each part if behaving.

This is exactly what I did, then just built it back up again.

I ended up changing the formula to this and it’s starting working

Reforecast Dr/Cr = 
VAR Income = CALCULATE( [Total Reforecast], 'Indexed Type'[Indexed Type] = "1. Income" )
VAR Expense = CALCULATE( [Total Reforecast], 'Indexed Type'[Indexed Type] = "2. Expenses" ) * -1

RETURN
DIVIDE(
IF( SELECTEDVALUE( 'Indexed Type'[Indexed Type] ) = "1. Income" ,
    Income, 
        IF( SELECTEDVALUE( 'Indexed Type'[Indexed Type] ) = "2. Expenses" ,
            Expense,
                Income + Expense )), 1)

So it’s something to do with the table dimension not matching up from what I can tell. You’ll have to dig deeper into that as you know the data better.

Chrs
Sam

Jawed · July 8, 2019, 7:08am

Thank you very much. I see the difference. It is most probably something to do with the dimension. I’ll have a look today.