Forum Discussion

MoMrCrane's avatar
MoMrCrane
Helper I
1 year ago
Solved

translation table for two databases

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. 

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.