topic Re: Filter groups base on group age in DAX Commands and Tips

Filter groups base on group age

AFDCO — Mon, 12 Jun 2023 11:39:46 GMT

Hi!,

I'm currently calculating %variation of last 12 month' sales of shopping centers and comparing it with same period last year (e.g. sales may2023-june2022 vs may2022- june2021... may2021- june2020... may2020- june2019, next month it will be shown june2023-july2022 vs june2022-july2021...). This is shown on a yearly line chart to show the trend of each year vs current.

Rent = 
CALCULATE(
    SUM('Factura'[importe_emitido]),
    'Facturas'[TConcepto] = "Rent" || 'Facturas'[Tipo_Concepto] = "Rent In advance"
)

Rent_L12M_Current = 
CALCULATE(
    [Rent],
    FILTER(ALLSELECTED(CALENDARIO), CALENDARIO[Año/mes]> [Max_Fecha]-100)
)

Rent_L12M = 
VAR anoMes = [Max_Date_Context]
RETURN
CALCULATE(
    [Rent],
    FILTER(ALLSELECTED(CALENDARIO), CALENDARIO[Año/mes] <= anoMes && CALENDARIO[Año/mes] > anoMes-100 )
)

Rent_L12M_%Var = 
[Rent_L12M_Current]-[Rent_L12M])/[Rent_L12M]

The issue comes when a shopping center is new and has less than 12 months of sales or was new at that momoment of comparison and had not 12 months at that moment. For instance if a center was new in january 2020, it should be taken into account in the comparison of may2023-june2022 vs may2022-june2021 and may2023-june2022 vs may2021-june2020, but not in may2023-june2022 vs may2020-june2019 because it didn' have invoicing over the whole period.

Does anyone know how could I solve the metric??

Thank you in advance

Re: Filter groups base on group age

adrianVE278 — Mon, 12 Jun 2023 13:48:59 GMT

In the case you are commenting, you want to evaluate the segment where there is data, that is, if a store opened in May-current vs one that opened in January of last year if you compare January-current (of last year) vs the new store you want Do you only have the segment of data it has (the history it has accumulated)?

Re: Filter groups base on group age

AFDCO — Sun, 18 Jun 2023 19:02:10 GMT

No, not really the comparison is not between stores, is between periods of the same stores. Data can be displayed store by store or multiple stores at the same time but data must be evalueted just in case the store has available data during the 12 months of the periods, if there are just 11 months available, then the store should be romeved of the aggregated result

Re: Filter groups base on group age

AFDCO — Fri, 30 Jun 2023 06:51:34 GMT

I solved this problem using parallelperiods combined with ALL and a variable table using SUMMARIZED.