DAX - Evolution total - Row context

[Anonymous]

 Hello, I have a problem with an evolution formula in dax 

Numbers in red are the values the "%Progression_AFN_apres_event" formula should use to compute the result in red.

Red value under %Progression_AFN_apres_event is the desired result. 

Red values under NB_AFN_après_event AND NB_AFN_N-1_apres_event are values that should be used to compute the formula

 

// Formula to update

%Progression_AFN_apres_event =
VAR a = [NB_AFN_après_event]
VAR b = [NB_AFN_N-1_apres_event]
RETURN
    CALCULATE( AVERAGEX(AFN,
    CALCULATE(
        IF(
            AND(a > 0, b > 0),
            DIVIDE(a - b, b, BLANK()),
            BLANK()
        ) )
       
    )) NB_AFN_après_event = 
 var a =  [Event_Date_Max] 
 var afn_ = CALCULATE(sum(AFN[AFN]),FILTER(AFN,AFN[DATE_CREATION]> a ))
return  afn_

NB_AFN_N-1_apres_event = 
    var a = EDATE([Event_Date_Max],-12)
    var today_ly = EDATE(TODAY(),-12)
    var b = CALCULATE(sum(AFN[AFN]), DATEADD( dim_date[Date],-1,YEAR ),dim_date[Date]>a, dim_date[Date] <= today_ly)
    return b 

Here is a sample of AFN table, feel free to tweak it

CODE_COURTIER	DATE_CREATION	AFN	Max_event_date
I40559	15/11/2022	27	26/09/2023
I41686	20/02/2023	27	12/06/2023
I96210	31/01/2022	25	22/06/2023
I107337	31/01/2023	21	12/06/2023
I41686	06/03/2023	21	12/06/2023
I41686	13/12/2022	18	12/06/2023
I96183	26/12/2022	17	12/06/2023
I96210	27/01/2022	16	22/06/2023
I96222	30/11/2022	15	12/06/2023
I98917	15/12/2022	12	20/06/2023
IA96461	23/01/2023	12	17/04/2023
IA97472	03/01/2022	12	08/06/2023
I97389	09/11/2022	12	20/06/2023
I97646	20/09/2022	11	31/05/2023
I96222	12/12/2022	10	12/06/2023
I97646	27/09/2022	10	31/05/2023
I41686	04/10/2022	9	12/06/2023
I107034	26/01/2023	8	22/06/2023
I97764	27/12/2022	8	22/06/2023
I107337	01/02/2023	8	12/06/2023

[Anonymous]

up