DAX - Evolution total - Row context

Anonymous — Mon, 30 Oct 2023 15:55:01 GMT

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

Re: DAX - Evolution total - Row context

Anonymous — Tue, 31 Oct 2023 09:01:07 GMT

topic DAX - Evolution total - Row context in DAX Commands and Tips

DAX - Evolution total - Row context

Re: DAX - Evolution total - Row context