Forum Discussion

feralvarez994's avatar
feralvarez994
Helper II
3 years ago

Calculated table

Hi everyone, i have the following calculated table

TablaSemestres =
ADDCOLUMNS(
    GENERATESERIES(
        DATE(2000, 1, 1),
        DATE(2025, 12, 31),
        183
    ),
    "Fecha de compra",
        DATE(YEAR([Value]), IF(MONTH([Value]) <= 6, 1, 7), 1),
    "Fecha de venta",
        EOMONTH(DATE(YEAR([Value]), IF(MONTH([Value]) <= 6, 6, 12), 1), 0)
)
And the following calculated column
Stock =
-- CONTAR AUTOS COMPRADOS ANTES DE LA FECHA DE COMPRA Y VENDIDOS DESPUES DE LA FECHA DE VENTA (O QUE FECHA DE VENTA ESTÉ EN BLANCO),
-- ES DECIR, COMPRADOS VENDIDOS + COMPRADOS SIN VENDER,
-- QUE NO SEAN FLOTA NI OBSOLETA
var buydate = TablaSemestres[Fecha de compra]
var selldate = TablaSemestres[Fecha de venta]
var stock =
CALCULATE(
    COUNT(AUTOS[AT_COAUTO]),
    AUTOS[FECHA COMPRA] <= buydate
    &&AUTOS[Fecha Reserva] >= selldate || AUTOS[Fecha Reserva] = BLANK(),
    CLAAUTOS[CLAU_DETALLE] <> "FLOTA" && CLAAUTOS[CLAU_DETALLE] <> "OBSOLETA",
    ALL(TablaSemestres)
)
return IF(TablaSemestres[Value] > TODAY(), 0, stock)

 

I want to add a new column in the calculated table with the different cities of the table (AUTOS) to get the stock of every city each semester. Can someone help me ?

1 Reply

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

    Instead of GENERATESERIES use a proper calendar table that includes a semester/half year column.