How to avoid row interaction in a DAX

[LaurenceA]

I have made a DAX measure that calculate the sales of the last 2 years. 
I use this measure in a table and filter to isolated only the customer with X$ or less. So far so Good. 

But when I add the Sales reps it will return the line for each sales rep. wich I don't want. Is there a wat to avoid this. 
My goal is to see which client did not buy for the last two year and did buy this year and to see the sales rape associated with the current year sales. 

Here is the DAX 

_InvoiceNetAmountCAD_Sum_Last2years =

VAR _ThisYear = YEAR( TODAY() ) -- Retourne l'année en cours (Ex: 2025)

VAR _LastYear = _ThisYear - 1 -- Retourne l'année dernière basé sur l'année en cours (Ex: 2024)

VAR _SecondToLastYear = _ThisYear -2 -- Retourne l'avant année dernière basé sur l'année en cours ( Ex: 2023)

VAR _NetAmountLastYear =

CALCULATE(

    [_ConsolidatedInvoiceNetAmountCAD_Sum], -- Somme le Invoice Net Amount CAD

        FILTER( ALL('Billing Date'),

            YEAR('Billing Date'[Billing Date]) = _LastYear -- Si l'année de facturation est égale à celle de l'année dernière

        )

)

VAR _NetAmountSecondToLastYear =

CALCULATE(

    [_ConsolidatedInvoiceNetAmountCAD_Sum],  -- Somme le Invoice Net Amount CAD

        FILTER( ALL( 'Billing Date'),

            YEAR('Billing Date'[Billing Date]) = _SecondToLastYear -- Si l'année de facturation est égale à celle d'il y a deux an

        )

)

RETURN

 _NetAmountLastYear +_NetAmountSecondToLastYear -- Retourne le Invoice Net AMount CAD des deux dernières années

[v-saisrao-msft]

Hi @LaurenceA,

We haven’t heard back from you regarding your issue. If it has been resolved, please mark the helpful response as the solution and give a ‘Kudos’ to assist others. If you still need support, let us know.

Thank you.

[v-saisrao-msft]

Hi @LaurenceA,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-saisrao-msft]

Hi @LaurenceA,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[v-saisrao-msft · Accepted solution]

Hi @LaurenceA,

Thank you for reaching out to the Microsoft Fabric Forum Community.

It sounds like the issue arises when adding the Sales Rep field to your table. The measure you created correctly calculates sales for the last two years, but when Sales Rep is added, the visual breaks into multiple rows per customer due to the measure being re-evaluated in the context of each rep. 

Below is the revised Dax Measure 

_InvoiceNetAmountCAD_Sum_Last2years =
VAR _ThisYear = YEAR(TODAY())
VAR _LastYear = _ThisYear - 1
VAR _SecondToLastYear = _ThisYear - 2

VAR _NetAmountLastYear =
    CALCULATE(
        [_ConsolidatedInvoiceNetAmountCAD_Sum],
        FILTER(
            ALL('Billing Date'),
            YEAR('Billing Date'[Billing Date]) = _LastYear
        ),
        REMOVEFILTERS('Sales Rep')
    )

VAR _NetAmountSecondToLastYear =
    CALCULATE(
        [_ConsolidatedInvoiceNetAmountCAD_Sum],
        FILTER(
            ALL('Billing Date'),
            YEAR('Billing Date'[Billing Date]) = _SecondToLastYear
        ),
        REMOVEFILTERS('Sales Rep')
    )

RETURN
    _NetAmountLastYear + _NetAmountSecondToLastYear

If this solution doesn’t fully resolve the issue or you’re seeing unexpected behavior in the visual, would you mind sharing a small sample of your dataset? 
 
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you. 

[Greg_Deckler]

@LaurenceA There is probably a way. However, the right answer is going to be heavily dependent on your semantic model. 

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.