How to Use Budget when Actuals are missing

[fcastle]

Hi I have two tables, ActualTrans and BudgetTrans, which contain as you may suspect actuals and budget, for the sake of the argument we could say there are 4 columns in each table:

ActualTrans

Transdate, Company, Account, Amount

BudgetTrans

TransDate, Company, Account, Amount

What I want to achieve is to have the TotalYTD to conditionally use BudgetTrans[Amount]) or ActualTrans[Amount] depending on whether I have actuals or not for a company in that period. Meaning that IF I filter my dashboard on i.e. January to August 2016, and only 10 out of the 35 companies in that point of time has closed their accounts  I want to use actuals for the 10 companies in all eight periods, while for the remaining 25 I want to use the actuals for the first 7 months and budget for the last .

I'm able to achieve this using a crossjoin table where I can aggregate my actuals and budgets per month and company and then apply some simple if statements to choose whether to use either budget or actuals for a company in a specific month.

But there must be a more elegant way to achieve the same thing, which doesn’t depend upon an aggregation table and fixes this using DAX statements.

If this doesn’t make sense I can add that this is to give a prediction for the aggregated group EBIT and Revenue while the accounts are in the process of being closed.

any insights are greatly appreciated.

BR

FC

[Framet · Accepted solution]

Perhaps this will work. I can't test so I apologise if it is way off. Note the addition of the two filtered measures is to avoid utilising IF however which method performs better in your data model could vary.

Let's assume for now you have a table holding Accounts, Companies, Periods with the approptiate relationships and that there will be no transactions in ActualTrans unless the period is closed.

ActualsOrBudgets :=
SUMX (
    'Companies',
    CALCULATE (
        SUMX ( 'Periods', ActualTrans[Amount] ),
        FILTER (
            ActualTrans,
            CALCULATE ( COUNTROWS ( ActualTrans ), ALL ( 'Accounts' ) ) > 0
        )
    )
)
    + SUMX (
        'Companies',
        CALCULATE (
            SUMX ( 'Periods', BudgetTrans[Amount] ),
            FILTER (
                ActualTrans,
                CALCULATE ( COUNTROWS ( ActualTrans ), ALL ( 'Accounts' ) ) = 0
            )
        )
    )

[fcastle]

interesting, sounds like you are familiar with the issue. I considered the closed period approach for us as well, but went in a different direction. Regardless, I think your reply is a valid solution to the problem, albeit some minor deviation on the assumptions, but that is on me as I didn’t really provide you with a full list of requirements.

Marking your replay as the solution.

BR

fcastle

[v-haibl-msft]

@fcastle

Thanks for your description, but I’m still not so clear about your requirement. Could you please provide some sample data and the expected output to us?

Best Regards,

Herbert

[fcastle]

I had sorted this out by using calculated columns, applying conditional statements in order to choose whether to report actuals or one of the forcast models as the return value. 

Simply put I want to predict last month's ie revenue using either actual revenue or forcasted value for each company in the group, since we have 30+ companies, where a substantial number are import clients (meaning they do their accounts in their legacy system, which we subsequent import into the group ERP system when finished, and yes I'm also working on getting every company over on the same platform). This is rather simple to acheive using calculated columns and some if statements, and create my measures on those columns instead of writing everything into on big dax statement.

Thanks for taking the time to actually request more information.

[Greg_Deckler]

Sounds like a Measure with an IF statement.