Common ratios from data in two tables

[BryanSt]

I have two tables, one for historical actuals (up to the end of the current reporting year July based, from a source system updated quarterly) and another for forward looking budgets (including the current reporting year).  Both follow the same chart of accounts and data format.

I have calcuated a range of ratios in my Actual table based on data in that table (did that before I got access to the budget date).  I am now looking to do the same ratios in the Budget table, so I can see the ratios move between historical actuals and what is being budgeted in the future.

Here is my question, is there are way of having the same calculations happen on data from two tables depending on if the date is historical (in the past based on the current quarter for reporting) or bugdet (beyond the current reporting quarter)?  If so would these be in a seperate table as the historical table has data only up to the current reporting year and the budget table has data from the current reporting year and beyond, so the data (quarterly data) are different in both tables.  I do have a Date table with all quarters in it.

I have a parameter for the current reporting quarter (eg 31/12/2017) and another for the end of the reporting year (eg 30/06/2018).

I could replicate the ratios in the budget table (separate measures), but this would mean having two series of data on a report, which is doable but not as elegant as I would like.

Any hints and tips greatly appreciated.

[v-yuta-msft · Accepted solution]

Hi BryanSt,

You want to have only a serie of measures in one table which can have same calculation on two tables, right?

You have to create a relationship and express columns of another table using Table2[Column] or Related(Table2[Column]), the table names in the expression are different so that you should create two series of measures in two tables.

As a workaround, you can merge two tables based on some columns or append one table to another table.

Regards,

Jimmy Tao

[BryanSt]

I was thinking along that line, but for clarity-sake I copied the measures into a new table with all dates (actual and forecast) as the key.  I use the actuals data for various YTD calcs, so wanted to seperate them.

Adjusted the DAX based on a Parameter, which has the cut-over date between actuals and future budgets, to use data from either table based on its date and the cut-over date parameter.

Thanks