Average Cost Triangle/Triangulo de costo Medio

[leanmap]

Good afternoon,

I am trying to create an average cost triangle like the following example:

	1	2	3	4	5	6	7	8	9	10	11	12	13
2023 Q1	1994	3476	4340	4711	5556	6401	7246	8091	8936	9781	10626	11471
2023 Q2	8169	10639	11051	11463	11875	12287	12699	13111	13523
2023 Q3	3000	3084	3168	3252	3336	3420
2023 Q4	4780	5521	6262

The data shown is in 2023 Q1 row 1, it is the average cost of January only, then row 2 is the cumulative data of January, February, and row 3 is January, February, March, but in row 4 I need to get the average cost of the data of this quarter but with the data that was loaded in April with opening date in 2023 Q1 (January, February, March) which may have an increase or decrease.

What I have so far, I am doing it with the following formula

VAR valor = CALCULATE( DISTINCTCOUNT(BBDD[Id_Case]), USERRELATIONSHIP(Calendar[Date], BBDD[Open_Date]) ) RETURN CALCULATE( Valor, FILTER(ALL(BBDD[Id_Case]), BBDD[Id_Case] <= MAX(BBDD[Id_Case])) )

I did the same to calculate the cost and then divided the two measures.

Below is a sample table

Thank you in advance

---------------------------------------------------------------------------------------------------

Buenas tardes,

Estoy tratando de hacer un triangulo de coste como el siguiente ejemplo

	1	2	3	4	5	6	7	8	9	10	11	12	13
2023 Q1	1994	3476	4340	4711	5556	6401	7246	8091	8936	9781	10626	11471
2023 Q2	8169	10639	11051	11463	11875	12287	12699	13111	13523
2023 Q3	3000	3084	3168	3252	3336	3420
2023 Q4	4780	5521	6262

Los datos que se ven son en el 2023 Q1 fila 1, es el promedio de costos unicamente de Enero, luego en la fila 2 son los datos acumulados de Enero, Febrero, y en la 3 de Enero, Febrero, Marzo, pero en la fila 4 debo obtener el costo medio de los datos de este trimeste pero con los datos que se cargaron en Abril con fecha de apertura en el 2023 Q1 (Enero, Febrero, Marzo) que pueden tener un aumento o disminucion.

Lo que tengo hasta ahora, lo estoy haciendo con la siguiente formula:

VAR valor = CALCULATE(
                                       DISTINCTCOUNT(BBDD[Id_Case]), USERRELATIONSHIP(Calendar[Date], BBDD[Open_Date))

RETURN 

       CALCULATE(Valor,
                           FILTER(ALL(BBDD[Id_Case]), BBDD[Id_Case] <= MAX(BBDD[Id_Case])))

Hice lo mismo para calcular el costo y luego dividi las dos medidas.

Algunos datos de ejemplo de la tabla:

Id_Case	Open_Date	Charge_date	Cost
2500	10/01/2023	01/01/2023	1500
2504	13/01/2023	01/01/2023	1747
2578	16/01/2023	01/01/2023	1994
2652	19/01/2023	01/01/2023	2241
2726	22/01/2023	01/01/2023	2488
2800	25/01/2023	02/02/2023	2735
2874	28/01/2023	02/02/2023	2982
2948	31/01/2023	02/02/2023	3229
3022	03/02/2023	02/02/2023	3476
3096	06/02/2023	02/02/2023	3723
3170	09/02/2023	02/02/2023	3970
3244	12/02/2023	02/02/2023	4217
3318	15/02/2023	02/02/2023	4464
3392	18/02/2023	02/02/2023	4711
3466	21/02/2023	02/02/2023	4958
3540	24/02/2023	02/02/2023	5205
3614	27/02/2023	02/02/2023	5452
3688	02/03/2023	02/03/2023	5699
3762	05/03/2023	02/03/2023	5946
3836	08/03/2023	02/03/2023	6193
3910	11/03/2023	02/03/2023	6440
3984	14/03/2023	02/03/2023	6687
4058	17/03/2023	02/03/2023	6934
4132	20/03/2023	02/03/2023	7181
4206	23/03/2023	01/04/2023	7428
4280	26/03/2023	01/04/2023	7675
4354	29/03/2023	01/04/2023	7922
4428	01/04/2023	01/04/2023	8169
4502	04/04/2023	01/04/2023	8416
4576	07/04/2023	01/04/2023	8663
4650	10/04/2023	01/04/2023	8910
4724	13/04/2023	01/04/2023	9157
4798	16/04/2023	01/04/2023	9404
4872	19/04/2023	01/04/2023	9651
4946	22/04/2023	01/04/2023	9898
5020	25/04/2023	01/05/2023	10145
5094	28/04/2023	01/05/2023	10392
5168	01/05/2023	01/05/2023	10639
5242	04/05/2023	01/05/2023	10886
5316	07/05/2023	01/05/2023	11133
5390	10/05/2023	01/05/2023	11380
5464	13/05/2023	01/05/2023	11627
5538	16/05/2023	01/05/2023	11874
5612	19/05/2023	01/05/2023	12121
5686	22/05/2023	01/05/2023	12368
5760	25/05/2023	01/05/2023	12615
5834	28/05/2023	01/05/2023	12862
5908	31/05/2023	01/05/2023	13109

Muchisimas gracias!!

[leanmap]

exactly, and assign for each charge date, the date of the corresponding quarter.

Thanks

[leanmap]

Hello Ibendlin
My output is the average of the cost column, it depends on the Open_Date and Charge data column, and I have to visualize it by quarter, for example I add what I would have to see in each box of the table, if it is not clear let me know:

1

2

3

4

5

6

7

8

9

10

11

12

2023 Q1

January (Avg Cost Open_Date and Charge_Date January)

Febreary (Avg Cost Open_Date and Charge_Date January + February)

March Febreary (Avg Cost Open_Date and Charge_Date January + February + March)

April (Avg Cost Open_Date (January + February + March) but Charge_Date (April)

May

June

July

August

September

2023 Q2

April  (Avg Cost Open_Date and Charge_Date April)

May (Avg Cost Open_Date and Charge_Date April + May)

June (Avg Cost Open_Date and Charge_Date April + May +June)

July (Avg Cost Open_Date (April + May + June) but Charge_Date (July)

August

September

October

November

2023 Q3

July

August

September

October

November

December

2023 Q4

October

November

December

January (Next Year)

2024 Q1

My sample data is from only 5 months, but my actual data spans from 2020 onward.

I leave you images of what I have:

Open_date = Fecha_Ocurrencia
Charge_date = Fecha_Carga
Cost = Carga Siniestral

Thank in advance

[lbendlin]

So for January you would want to ignore the entries that have a charge date in February?

[lbendlin]

What are the columns in your expected output?  weeks in the quarter?

Your sample data only has five months?