Sum not adding up correctly

[fazza1991]

Hi,

I am trying to correct the formula below

Measure 3 = 

CALCULATE([Measure 4],  
   DATESINPERIOD ( 
        'Date'[Date],                 
        EOMONTH ( MAX ( 'Terminated|SF'[Notice_Date] ), 0 ), 
        -12,  MONTH))

I am trying to get the last 12 months revenue from date of termination but the information is stored on different tables.

 Table 1: contain revenue per month

Report Month	Account Id	Account Name	Revenue
01/12/2020	2	2 ltd	10
01/01/2021	1	1 ltd	10
01/02/2021	1	1 ltd	10
01/03/2021	1	1 ltd	10

Table 2: contain date client terminated

Notice date	Account Id	Account Name	Reason Left
01/03/2021	1	1 ltd	Competitor

Table 3: Desired Results

Account Name	Last 12 months Revenue	Reason left
1 ltd	30	Competitor

The above formula allows me to calculate the last 12 months revenue, when slicer is used to show the month reporting on it displays the correct client and revenue but the total includes all clients that may have terminated and the prior 12 months

revenue eg.

Table 4: unwanted results

Account Name	Last 12 months revenue	Reason Left
1 ltd	30	competitor
Total	40

I have also a calendar table linked to Report MOnth and Notice date

Thanks in advance

[Anonymous · Accepted solution]

Hi @fazza1991 ,

According to your description, here are my steps you can follow as a solution.

(1)My test data is the same as yours.

(2) We can create  a measure.

Last 12 months Revenue = 
CALCULATE (
    SUM ( 'Table 1'[Revenue] ),
    FILTER (
        'Table 1',
        [Report Month] >= EOMONTH ( MAX ( 'Table 2'[Notice date] ), -12 )
            && [Report Month] <= MAX ( 'Table 2'[Notice date] )
    )
)

(3)Then the result is as follows.

Best Regards,

Neeko Tang

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