Forecast to Actuals formula

Hi there, I have modelled CAPEX which is coming from an Excel (live query connection to SharePoint).

In there, the users submit their CAPEX request using a specif KEY (which is also entered in SAP - that’s the unique common reference across the two).

Then I have the Actual spend pulled out of SAP in Excel format that also contains the KEY.

I would like to create a formula that deducts the Actual value from the Forecast (using this KEY).

Forecast:

Actuals:

Any ideas to overcome this are welcome.

Thanks!

@SamSPAIN

Do you have a PBIX file you built with the data in it that you can share. It would be lot quicker else I will have to use your screen shot to create one but chances are I might misinterpret some information,

Thanks,
Joe

Hi @SamSPAIN

  1. You need to define a Difference Measure that could be like
    Difference = sum(Actuals[ActualAmount])-sum(Budget[BudgetAmount])

  2. Yo need to create a bridge table containing the projects because PBI does not support many to many relationships.

I have attached a simple example. I appreciate if you can check the question as solved

Saludos!
Diego

Finance Solution.pbix (50.3 KB)

FYI, PBI Does support Many-to-Many relationships, PowerPivot on the other hand does not. Not saying m2m is the correct way to go here, but it is possible.

Thanks for posting your question @SamSPAIN. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question.

Ok, thanks for the feedback!

Hi @SamSPAIN, we’ve noticed that no response has been received from you since the 10th of June. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

All: @EnterpriseDNA, @Nick_M, @diego, @joedias first off sorry for such - business is been really tough these days. My sincere apologies for not getting back to you on this way before.

Find enclosed a sample of the PBIX, along with the explanation regarding the outcome I need to overcome:

CapEx Financial Reporting (3).pbix (760.5 KB)

Goal is to link Forecast to Actuals based on a KEY and apportion the difference for a closed period (typically a quarter) to next periods within the same year.

Please let me know if this is still unclear.

Thanks a lot for sharing your thoughts here.

Hi @SamSPAIN,

Can you give this a go.

FC Diff = 
VAR ThisQ = SELECTEDVALUE( 'Calendar'[CurQuarterOffset] )
VAR ThisY = SELECTEDVALUE( 'Calendar'[Year] )
VAR FirstQ = CALCULATE( MIN('Calendar'[CurQuarterOffset] ), FILTER( ALL( 'Calendar' ), 'Calendar'[Year] = ThisY ))
VAR vTable =
    ADDCOLUMNS(
        SUMMARIZE( 'Calendar', 'Calendar'[Quarter] ), 
        "@Remainder", CALCULATE( [CapEx USD], FILTER( ALL( 'Calendar' ), 'Calendar'[CurQuarterOffset] >= FirstQ && 'Calendar'[CurQuarterOffset] <= ThisQ )) - 
            CALCULATE( [Spend], FILTER( ALL( 'Calendar' ), 'Calendar'[CurQuarterOffset] >= FirstQ && 'Calendar'[CurQuarterOffset] <= ThisQ ))
    )
RETURN

IF( ISBLANK( [Spend] ), 
    BLANK(),
    SUMX( vTable, [@Remainder] )
) 

And

FC Adj. = 
VAR ThisY = SELECTEDVALUE( 'Calendar'[Year] )
VAR FirstQ = CALCULATE( MIN('Calendar'[CurQuarterOffset] ), FILTER( ALL( 'Calendar' ), 'Calendar'[Year] = ThisY ))
RETURN

IF( SELECTEDVALUE( 'Calendar'[CurQuarterOffset] ) = FirstQ, 
    [CapEx USD],
    IF( ISBLANK( [Spend] ), BLANK(),
    [CapEx USD] + CALCULATE( [FC Diff], DATEADD( 'Calendar'[Date], -1, QUARTER ))
))

.
With this result.

image

I hope this is helpful.

1 Like

Hi All

I am adding to this thread with a related question (am new here, so hope this is ok?!)

I am dealing with financial data with a portfolio of companies (providers) and my P&L data table has these columns:

P&L Line and P&L Statement are essentially the same thing, just utilising better format/wording in my template based on P&L Statement column. Date is an actual date (linked to calendar table but not relevant to what I am doing). Fiscal Quarter column contains “Q1”, or “Q2”, or “Q3”, or “Q4”, and the remaining columns allow me to figure out what the fiscal year is - each company in the portfolio may have a different fiscal year end.

I have set up a template table to display results in P&L format using the approach taken in Enterprise DNA financial reporting course and have succesfully shown actual and budget data in two separate tables, broken down by Q1 - Q4 using measures. I have a page filter for provider and fiscal year (FY).

Currently I have two separate tables, one filtered for Actual and one for Budget which works fine. I am just not sure I will be able to create a column that shows YTD actual and budget, including the margin calculations, dependent on when the latest actual quarter data is.

My quarterly data is displayed in both tables using the following measure (including measures for margins which are calculated from my data set:

Q1 =
VAR CurrentItem = SELECTEDVALUE(‘Template_Group_PL’[P&L Statement])

Return
SWITCH( TRUE(),
CurrentItem = “EBITDARM Margin”, FORMAT([Grp_EBITDARM_Mgn_Q1],“0.0%”),
CurrentItem = “EBITDAR Margin”, FORMAT([Grp_EBITDAR_Mgn_Q1],“0.0%” ),
CurrentItem = “EBITDA (p/e) Margin”, FORMAT([Grp_EBITDA_PE_Mgn_Q1],“0.0%”),
CurrentItem = “Group EBITDA Margin”, FORMAT([Grp_EBITDA_Mgn_Q1], “0.0%” ),
CurrentItem = “Total Interest”, [Total Interest_Q1],

SUMX(FILTER(‘Group P&L’,‘Group P&L’[P&L Statement] = CurrentItem && ‘Group P&L’[Fiscal Quarter]=“Q1”),‘Group P&L’[Value])
)

This is repeated in a measure for each quarter.

Now I am trying to calculate a YTD figure for both the actual and budget values that will depend on when the last actual quarter of data is, so that I am comparing apples with apples (budget will always have full year of data).
My approach was to build measures to check if the actual income in that quarter was greater than zero, example:

Grp_Q4_Test =

sumx(FILTER(‘Group P&L’,‘Group P&L’[P&L Line]=“Group Income”&& ‘Group P&L’[Fiscal Quarter]=“Q4” && ‘Group P&L’[Actual/Budget]=“Actual”),‘Group P&L’[Value])

I have one of these for each quarter.

I then built a YTD measure like this:

Year to Date =

VAR CurrentItem = SELECTEDVALUE(‘Template_Group_PL’[P&L Statement])

Return
SWITCH( TRUE(),
[Grp_Q4_Test]>0, SUMX (FILTER(‘Group P&L’,‘Group P&L’[P&L Statement] = CurrentItem && ‘Group P&L’[Fiscal Quarter]=“Q1”||“Q2”||“Q3”||“Q4”),‘Group P&L’[Value]),
[Grp_Q3_Test]>0, SUMX (FILTER(‘Group P&L’,‘Group P&L’[P&L Statement] = CurrentItem && ‘Group P&L’[Fiscal Quarter]=“Q1”||“Q2”||“Q3”),‘Group P&L’[Value]),
[Grp_Q2_Test]>0, SUMX (FILTER(‘Group P&L’,‘Group P&L’[P&L Statement] = CurrentItem && ‘Group P&L’[Fiscal Quarter]=“Q1”||“Q2”),‘Group P&L’[Value]),

SUMX (FILTER('Group P&L','Group P&L'[P&L Statement] = CurrentItem && 'Group P&L'[Fiscal Quarter]="Q1"),'Group P&L'[Value])

)

I don’t know if this is correct, or just fluke, but it works on my table with the actual data in it (table has filter for “Actual” results only. It does not yet deal with the margin measures…

My issue is trying to get it to work for the budget calculation. I cannot add an additional && to filter for “budget” only in the Actual/Budget column and I am stuck for ideas on how to resolve. When I add the measure that works in the table filtered to actual, into the table filtered to budget, it defaults to only show Q1 budget data as I guess the table filter is interfering?

Essentially, if only Q1-Q3 have actual income greater than zero, I want this measure to count all the results for budget Q1-Q3 as well and not include Q4.

Is this possible? I would be happy to show this in a separate table within the report, if I could get it to calculate correctly! I would also like to include the margin calculation measures in this data if at all possible.

Any help much appreciated!

Thanks.

Hi @Joanna25, to help you solved your query and more visibility please start a new topic within the forum. You may check this how-to guide for reference - How To Use The Enterprise DNA Support Forum https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951. Thanks!

Hi @Melissa. First, thank you very much for taking the time to reply.

Whilst your code is great, it isn’t providing what I’d expect. Apologies if I wasn’t clear before.

Your code is always restating the Fct (CapEx USD) based on Fct to Act KEY(Applicant) which is good. However, fore closed periods like Q1 for 2020 the Fct should be overwritten by Actuals and the Q1 Fct to Act delta, the $ 92.31K should be reduced from next Q forecast.

So Q1 delta is correct, meaning we overspent by 92.31K and the final amount for this project would be the Act (Spend) that overwrites the Fct, so it would be $ 1,258M.

Ideally, the delta (either over or under spend) would be diluted within the next quarters that are left (still forecast), in this case that would be 92.31K to reduce from Q2, Q3 and Q4 CapEx USD (Forecast).

So the Fct Adj for the project within the current year 2020 should remain at $ 3,225 until we close the year. Meaning we are always holding to the Forecast figure which gets adjusted CapEx Financial Reporting (3).pbix (765.7 KB) in the future Q according to prior Q delta

This is already in as showcased in the matrix - not sure though how can I best put that all together.

Last but not least, I believe there’s an issue because when i put together Fct (CapEx USD) and Act (Spend) within the matrix, I get all KEYs although I was filtering by one Company (India).

I truly appreciate your support here.

No worries, see if this works for you @SamSPAIN

FC Adj. v2 = 
VAR ThisY = SELECTEDVALUE( 'Calendar'[Year] )
VAR ThisQ = CALCULATE( MAX('Calendar'[CurQuarterOffset] ), FILTER( ALL( 'Calendar' ), 'Calendar'[Year] = ThisY && NOT( ISBLANK( [Spend] ))))
VAR FirstQ = CALCULATE( MIN('Calendar'[CurQuarterOffset] ), FILTER( ALL( 'Calendar' ), 'Calendar'[Year] = ThisY ))
RETURN 

SWITCH( TRUE(),
    ISINSCOPE('Calendar'[Quarter] ) && SELECTEDVALUE( 'Calendar'[CurQuarterOffset] ) = FirstQ && SELECTEDVALUE( 'Calendar'[CurQuarterOffset] ) < ThisQ, [Spend],
    ISINSCOPE('Calendar'[Quarter] ) && SELECTEDVALUE( 'Calendar'[CurQuarterOffset] ) = FirstQ, [CapEx USD],
    ISINSCOPE('Calendar'[Quarter] ) && SELECTEDVALUE( 'Calendar'[CurQuarterOffset] ) = ThisQ, [CapEx USD] + CALCULATE( [FC Diff], DATEADD( 'Calendar'[Date], -1, QUARTER )),
    ISINSCOPE('Calendar'[Quarter] ) && ISBLANK( [Spend] ), [CapEx USD],
    LOOKUPVALUE( 'Calendar'[CurYearOffset], 'Calendar'[Year], ThisY ) < 0, [Spend],
    [CapEx USD]
)

.
Hope this is helpful.

1 Like

This is terrific! sorry to keep bothering you but have some points left:

a) would you know any video/tutorial here in Enterprise DNA that covers something similar? I’m specially keen on learning how did you come up with this argument:

b) is there any way to “dilute” the $ 92K overspend within the YTG quarters instead of allocating it fully to next Q?

c) I think I have a fundamental issue with the modelling - see Page 2. When attempting to see Forecast and Actuals using Company and KEY (ProjectKey from Fct and Applicant from Act) I see KEYS falling to wrong Companies and actually being duplicated

d) We forecast CapEx every Q like 3+9, (Q1 Act, YTG Fct), 6+6 (Q1 and Q2 Act, Q3 and Q4 Fct) and so on… I have duplicated a snapshot of the 6+6 finalised which I would like to keep to see Forecast over Forecast comparisons. Is it possible to “store” an image of previous Forecast for comparisons?
imageCapEx Financial Reporting (3).pbix (779.2 KB)

@SamSPAIN

Please adhere to forum guidelines. Know that asking more than one question in any forum thread or asking question after question in the same forum thread around the same project or piece of development work is considered to be inappropriate.

a) Look into anything relating Virtual tables and the Time Intelligence Series Brian an I did because we also covered using Date table Offsets…

b) I would expect so. Think of how you want that figure to be allocated and use the Offsets in your date table to divide and project that figure forward over multiple Q.

c) the Model is what drives every analysis / DAX calculation, if you expect there is a problem. Stop, Investigate and Fix before you move on.

d) I’m not entirely sure what you mean by that but snapshots usually need to be exported to be preserved.
.

I can relate to the fact that there will always be more questions, I know I have many :wink: but if your initial question has been answered please mark the thread as solved. You can always create a new thread if further assistance is required down the line.

@Melissa you totally right - I should’ve kept this in mind.

Will check in-depth the Time Intelligence Series to grasp this concept.

Once more, thank you so much for your support. It’s been much appreciated.

Warm regards

1 Like

Hello:

This isn’t a question specific to this thread but I’m hopeful it will be answered.

I am looking to watch Sam’s video( if there is one) regarding his “Understanding Your Customers Better” PBIX file.

If I could be pointed in the right direction it would be very much appreciated.

Thanks,

Bill S.

you might want to start a new topic instead of adding to topic already closed. you will get better results :slight_smile: