Deduplicate by Contract Number And Sequence Number when adding Revenue from multiple Tables

[ZhenyuLei]

Hi,

I have a main Revenue table and four different topic tables with their own Revenue tables linked:

• CCA -> Revenue(Signup&CCA)
• CashSignUp -> Revenue(Signup&CCA)
• CallData -> Revenue(Call)
• Leads & Tradeshow -> Revenue(Leads & Tradeshow)
• Main Revenue Table: Revenue(eComm Total & NoneComm NEW)

And I have created Revenue measures in those topic tables:

For example

In CashSignUp:

• SignUp_Revenue =CALCULATE(SUM('Revenue(Signup&CCA)'[Revenue]),CashSignUp[Cash_Join_ID] <> "_")
• SignUp_Revenue SameMonth =

SUMX (

    FILTER (

        SUMMARIZECOLUMNS (

            'Revenue(Signup&CCA)'[ContractDateOut],

            CashSignUp[TimeStampCreated],

            "@revenue", CALCULATE ( CashSignUp[SignUp_Revenue] )

        ),

        EOMONTH ( 'Revenue(Signup&CCA)'[ContractDateOut], 0 ) = EOMONTH ( CashSignUp[TimeStampCreated], 0 )

    ),

    [@revenue]

)

Now I want to write a DAX for "total Revenue Same Month", it needs to be revenue by ContractNumber and  SequenceNumber from all those four Revenue tables, I can't simply add those "revenue same month" together because there might be duplicates across those four revenue tables, the same contract and sequence could exist in one or two or three or four revenue tables. So I need to deduplicate when adding, and I want to keep the "same month" logic. Thanks!

[Anonymous]

Hi @ZhenyuLei,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Vinay Pabbu

[Anonymous]

Hi @ZhenyuLei,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Vinay Pabbu

[Anonymous]

Hi @ZhenyuLei,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Vinay Pabbu

[Anonymous · Accepted solution]

Hi @ZhenyuLei,

Thank you for reaching out to Microsoft Fabric Community Forum.

Please find attached pbix file

In a real scenario, dates repeat in Revenue tables → relationship should be One-to-Many.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Regards,
Vinay Pabbu