translation table for two databases

[MoMrCrane]

Earlier this year we transitioned our ERP. The old and new ERP is the same software, but different instances. The tables in old/new ERP are named the same. They are 2 seperate databases. I'm attempting to calculate total revenue year to date using TOTALYTD. So I have to calculate from 1/1/2024 - 3/31/2024 in the old ERP and from 4/1/2024 - Today() in the new ERP. 

BilledYTD =
 //revenue from production ERP
TOTALYTD(SUM('Invoice Details'[ActAmount]), 'Date'[Date] ) 
+ 
//revenue from legacy ERP
TOTALYTD(SUM('Legacy_MRC_INVOICE_DETAILS'[ActAmount]), 'Date'[Date])

and have other measures:

RevenueYTDLegacy = 
//revenue from legacy ERP
TOTALYTD(SUM('Legacy_MRC_INVOICE_DETAILS'[ActAmount]), 'Date'[Date])

and 

RevenueYTDProd = 
//revenue from production ERP
TOTALYTD(SUM('Invoice Details'[ActAmount]), 'Date'[Date] )

My issue is that after we went live, we changed the names of some of our units so my measures calculating revenue isn't pulling data from some units in the old system because the names of our units in old system is different.

My date table is connected to both Legacy_MRC_INVOICE_DETAILS and Invoice Details. Both of the invoice tables are pointing to the production Units table 

How do I make a translation table to say, if you see AT266 in the legacy invoice table, sum up the invoices as if it is AT265-02 in production so that my BilledYTD measure will sum up invoices from both invoice tables properly per unit. As you can see there are many units we changed the name of when we went live with our production environment. 

[v-xianjtan-msft · Accepted solution]

Hi @MoMrCrane 

You can try to create a translation table manually. Click on the Home tab and select Enter Data. In the pop-up window, enter the mapping of the old unit name to the new unit name. For example, like this:

OldUnit	NewUnit
AT266	AT265-02
OldUnit2	NewUnit2

Create a one-to-many relationship between the translation table and the Legacy_MRC_INVOICE_DETAILS table.

Then create a calculated column in the Legacy_MRC_INVOICE_DETAILS table using the following DAX:

TranslatedUnit = 
LOOKUPVALUE(
    UnitTranslation[NewUnit],
    UnitTranslation[OldUnit], 'Legacy_MRC_INVOICE_DETAILS'[Unit],
    'Legacy_MRC_INVOICE_DETAILS'[Unit] 
)

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.