Simplificar DAX calcular importe por año y mes

[apenaranda]

Hola,

tengo la siguiente tabla donde la columna importe ventas es personalizada:

importe ventas = IF('Prevision'[any]=2023 && 'Prevision'[ambito venta]=1 && 'Prevision'[mes]=1,CALCULATE([Ventas Totales],DATESBETWEEN(Fechas[Date],DATE(2023,1,1),DATE(2023,1,31))),IF('Prevision'[any]=2023 && 'Prevision'[ambito venta]=1 && 'Prevision'[mes]=2,CALCULATE([Ventas Totales],DATESBETWEEN(Fechas[Date],DATE(2023,1,1),DATE(2023,2,31))),IF('Prevision'[any]=2023 && 'Prevision'[ambito venta]=1 && 'Prevision'[mes]=3,CALCULATE([Ventas Totales],DATESBETWEEN(Fechas[Date],DATE(2023,1,1),DATE(2023,3,31))),IF('Prevision'[any]=2023 && 'Prevision'[ambito venta]=1 && 'Prevision'[mes]=4,CALCULATE([Ventas Totales],DATESBETWEEN(Fechas[Date],DATE(2023,1,1),DATE(2023,4,31))),IF('Prevision'[any]=2023 && 'Prevision'[ambito venta]=1 && 'Prevision'[mes]=5,CALCULATE([Ventas Totales],DATESBETWEEN(Fechas[Date],DATE(2023,1,1),DATE(2023,5,31))),IF('Prevision'[any]=2023 && 'Prevision'[ambito venta]=1 && 'Prevision'[mes]=6,CALCULATE([Ventas Totales],DATESBETWEEN(Fechas[Date],DATE(2023,1,1),DATE(2023,6,31))),IF('Prevision'[any]=2023 && 'Prevision'[ambito venta]=1 && 'Prevision'[mes]=7,CALCULATE([Ventas Totales],DATESBETWEEN(Fechas[Date],DATE(2023,1,1),DATE(2023,7,31))),IF('Prevision'[any]=2023 && 'Prevision'[ambito venta]=1 && 'Prevision'[mes]=8,CALCULATE([Ventas Totales],DATESBETWEEN(Fechas[Date],DATE(2023,1,1),DATE(2023,8,31))),IF('Prevision'[any]=2023 && 'Prevision'[ambito venta]=1 && 'Prevision'[mes]=9,CALCULATE([Ventas Totales],DATESBETWEEN(Fechas[Date],DATE(2023,1,1),DATE(2023,9,31))),IF('Prevision'[any]=2023 && 'Prevision'[ambito venta]=1 && 'Prevision'[mes]=10,CALCULATE([Ventas Totales],DATESBETWEEN(Fechas[Date],DATE(2023,1,1),DATE(2023,10,31))),IF('Prevision'[any]=2023 && 'Prevision'[ambito venta]=1 && 'Prevision'[mes]=11,CALCULATE([Ventas Totales],DATESBETWEEN(Fechas[Date],DATE(2023,1,1),DATE(2023,11,31))),IF('Prevision'[any]=2023 && 'Prevision'[ambito venta]=1 && 'Prevision'[mes]=12,CALCULATE([Ventas Totales],DATESBETWEEN(Fechas[Date],DATE(2023,1,1),DATE(2023,12,31)
))))))))))))))

Como se ve, voy calculando el importe de ventas por cada año y mes pero quisiera saber si hay posibilidad de reducir el codigo DAX y que no sea tan largo. La parte de codigo solo es 2023 pero en realidad quiero poner para 2022, 2023 y posteriormente con futuros años 2024...

Gracias.

[Syndicate_Admin · Accepted solution]

with both 2022 and 2023, 2024 ... kind of a loop for

No creo que se pueda hacer en una columna calculada.

[apenaranda]

Entiendo, pero eso solo hace que calcule o 2022 o 2023 segun ponga.

Lo que quisiera es que haga solo todo el recorrido tanto con 2022 como 2023, 2024...tipo un bucle for

[Syndicate_Admin]

VAR _Year = 2022

VAR _Year = 2023

[apenaranda]

perdona, me podrias indicar que cambios hay? parece igual que el primero. No entiendo donde hace el recorrido de 2022, posteriormente 2023...

[Syndicate_Admin]

Prueba esto

importe ventas = 

VAR _ambitoventa = 'Prevision'[ambito venta]
VAR _any = 'Prevision'[any]
VAR _mes = 'Prevision'[mes]
VAR _Year = 2022

VAR _d2 = DATE(_Year,1,1)

VAR _d2 = DATE(_Year,1,31)
VAR _d3 = DATE(_Year,2,31)
VAR _d4 = DATE(_Year,3,31)
VAR _d5 =DATE(_Year,4,31)
VAR _d6 =DATE(_Year,5,31)
VAR _d7 = DATE(_Year,6,31)
VAR _d8= DATE(_Year,7,31)
VAR _d9 = DATE(_Year,8,31)
VAR _d10 = DATE(_Year,9,31)
VAR _d11 = DATE(_Year,10,31)
VAR _d12 = DATE(_Year,11,31)
VAR _d13 = DATE(_Year,12,31)

RETURN
SWITCH( TRUE(), 
    _any = _Year&& _ambitoventa = 1&& _mes = 1,
            CALCULATE ( [Ventas Totales], DATESBETWEEN ( Fechas[Date], _d1, _d2 ) ),
    _any = _Year&& _ambitoventa = 1&& _mes = 2,
            CALCULATE ( [Ventas Totales], DATESBETWEEN ( Fechas[Date], _d1, _d3 ) ),
    _any = _Year&& _ambitoventa = 1&& 'Prevision'[ mes] = 3,
            CALCULATE ( [Ventas Totales], DATESBETWEEN ( Fechas[Date], _d1, _d4 ) ),
    _any = _Year&& _ambitoventa = 1&& _mes = 4,
            CALCULATE ( [Ventas Totales], DATESBETWEEN ( Fechas[Date], _d1, _d5 ) ),
    _any = _Year&& _ambitoventa = 1&& _mes = 5,
            CALCULATE ( [Ventas Totales], DATESBETWEEN ( Fechas[Date], _d1, _d6 ) ),
    _any = _Year&& _ambitoventa = 1&& _mes = 6,
            CALCULATE ( [Ventas Totales], DATESBETWEEN ( Fechas[Date], _d1, _d7 ) ),
     _any = _Year&& _ambitoventa = 1&& _mes = 7,
            CALCULATE ( [Ventas Totales], DATESBETWEEN ( Fechas[Date], _d1, _d8 ) ),
    _any = _Year&& _ambitoventa = 1&& _mes = 8,
            CALCULATE ( [Ventas Totales], DATESBETWEEN ( Fechas[Date], _d1, _d9 ) ),
    _any = _Year&& _ambitoventa = 1&& _mes = 9,
            CALCULATE ( [Ventas Totales], DATESBETWEEN ( Fechas[Date], _d1, _d10 ) ),
    _any = _Year&& _ambitoventa = 1&& _mes = 10,
            CALCULATE ( [Ventas Totales], DATESBETWEEN ( Fechas[Date], _d1, _d11 ) ),
    _any = _Year&& _ambitoventa = 1&& _mes = 11,
            CALCULATE ( [Ventas Totales], DATESBETWEEN ( Fechas[Date], _d1, _d12 ) ),
    _any = _Year&& _ambitoventa = 1&& _mes = 12,
            CALCULATE ( [Ventas Totales], DATESBETWEEN ( Fechas[Date], _d1, _d13 ) )
    )

[apenaranda]

calcula solo 2022

[Syndicate_Admin]

Prueba esto

importe ventas = 

VAR _ambitoventa = 'Prevision'[ambito venta]
VAR _any = 'Prevision'[any]
VAR _mes = 'Prevision'[mes]
VAR _Year = 2022

VAR _d2 = DATE(_Year,1,1)

VAR _d2 = DATE(_Year,1,31)
VAR _d3 = DATE(_Year,2,31)
VAR _d4 = DATE(_Year,3,31)
VAR _d5 =DATE(_Year,4,31)
VAR _d6 =DATE(_Year,5,31)
VAR _d7 = DATE(_Year,6,31)
VAR _d8= DATE(_Year,7,31)
VAR _d9 = DATE(_Year,8,31)
VAR _d10 = DATE(_Year,9,31)
VAR _d11 = DATE(_Year,10,31)
VAR _d12 = DATE(_Year,11,31)
VAR _d13 = DATE(_Year,12,31)

RETURN
SWITCH( TRUE(), 
    _any >= _Year&& _ambitoventa = 1&& _mes = 1,
            CALCULATE ( [Ventas Totales], DATESBETWEEN ( Fechas[Date], _d1, _d2 ) ),
    _any >= _Year&& _ambitoventa = 1&& _mes = 2,
            CALCULATE ( [Ventas Totales], DATESBETWEEN ( Fechas[Date], _d1, _d3 ) ),
    _any >= _Year&& _ambitoventa = 1&& 'Prevision'[ mes] = 3,
            CALCULATE ( [Ventas Totales], DATESBETWEEN ( Fechas[Date], _d1, _d4 ) ),
    _any >= _Year&& _ambitoventa = 1&& _mes = 4,
            CALCULATE ( [Ventas Totales], DATESBETWEEN ( Fechas[Date], _d1, _d5 ) ),
    _any >= _Year&& _ambitoventa = 1&& _mes = 5,
            CALCULATE ( [Ventas Totales], DATESBETWEEN ( Fechas[Date], _d1, _d6 ) ),
    _any >= _Year&& _ambitoventa = 1&& _mes = 6,
            CALCULATE ( [Ventas Totales], DATESBETWEEN ( Fechas[Date], _d1, _d7 ) ),
     _any >= _Year&& _ambitoventa = 1&& _mes = 7,
            CALCULATE ( [Ventas Totales], DATESBETWEEN ( Fechas[Date], _d1, _d8 ) ),
    _any >= _Year&& _ambitoventa = 1&& _mes = 8,
            CALCULATE ( [Ventas Totales], DATESBETWEEN ( Fechas[Date], _d1, _d9 ) ),
    _any >= _Year&& _ambitoventa = 1&& _mes = 9,
            CALCULATE ( [Ventas Totales], DATESBETWEEN ( Fechas[Date], _d1, _d10 ) ),
    _any >= _Year&& _ambitoventa = 1&& _mes = 10,
            CALCULATE ( [Ventas Totales], DATESBETWEEN ( Fechas[Date], _d1, _d11 ) ),
    _any >= _Year&& _ambitoventa = 1&& _mes = 11,
            CALCULATE ( [Ventas Totales], DATESBETWEEN ( Fechas[Date], _d1, _d12 ) ),
    _any >= _Year&& _ambitoventa = 1&& _mes = 12,
            CALCULATE ( [Ventas Totales], DATESBETWEEN ( Fechas[Date], _d1, _d13 ) )
    )

[apenaranda]

Hola,

parece que funciona, la pregunta es:

el ejemplo que me pones, veo que es para 2023

¿podria hacer algo en la parte de los calculos para que haga a partir del año 2022 hacia adelante?

[Syndicate_Admin]

Por favor, pruebe esto

importe ventas = 

VAR _ambitoventa = 'Prevision'[ambito venta]
VAR _any = 'Prevision'[any]
VAR _mes = 'Prevision'[mes]
VAR _Year = 2023
VAR _d1 = DATE(2023,1,31)
VAR _d2 = DATE(_Year,1,1)
VAR _d3 = DATE(2023,2,31)
VAR _d4 = DATE(_Year,3,31)
VAR _d5 =DATE(_Year,4,31)
VAR _d6 =DATE(_Year,5,31)
VAR _d7 = DATE(_Year,6,31)
VAR _d8= DATE(_Year,7,31)
VAR _d9 = DATE(_Year,8,31)
VAR _d10 = DATE(_Year,9,31)
VAR _d11 = DATE(_Year,10,31)
VAR _d12 = DATE(_Year,11,31)
VAR _d13 = DATE(_Year,12,31)

RETURN
SWITCH( TRUE(), 
    _any = _Year&& _ambitoventa = 1&& _mes = 1,
            CALCULATE ( [Ventas Totales], DATESBETWEEN ( Fechas[Date], _d1, _d0 ) ),
    _any = _Year&& _ambitoventa = 1&& _mes = 2,
            CALCULATE ( [Ventas Totales], DATESBETWEEN ( Fechas[Date], _d2, _d3 ) ),
    _any = _Year&& _ambitoventa = 1&& 'Prevision'[ mes] = 3,
            CALCULATE ( [Ventas Totales], DATESBETWEEN ( Fechas[Date], _d1, _d4 ) ),
    _any = _Year&& _ambitoventa = 1&& _mes = 4,
            CALCULATE ( [Ventas Totales], DATESBETWEEN ( Fechas[Date], _d1, _d5 ) ),
    _any = _Year&& _ambitoventa = 1&& _mes = 5,
            CALCULATE ( [Ventas Totales], DATESBETWEEN ( Fechas[Date], _d1, _d6 ) ),
    _any = _Year&& _ambitoventa = 1&& _mes = 6,
            CALCULATE ( [Ventas Totales], DATESBETWEEN ( Fechas[Date], _d1, _d7 ) ),
     _any = _Year&& _ambitoventa = 1&& _mes = 7,
            CALCULATE ( [Ventas Totales], DATESBETWEEN ( Fechas[Date], _d1, _d8 ) ),
    _any = _Year&& _ambitoventa = 1&& _mes = 8,
            CALCULATE ( [Ventas Totales], DATESBETWEEN ( Fechas[Date], _d1, _d9 ) ),
    _any = _Year&& _ambitoventa = 1&& _mes = 9,
            CALCULATE ( [Ventas Totales], DATESBETWEEN ( Fechas[Date], _d1, _d10 ) ),
    _any = _Year&& _ambitoventa = 1&& _mes = 10,
            CALCULATE ( [Ventas Totales], DATESBETWEEN ( Fechas[Date], _d1, _d11 ) ),
    _any = _Year&& _ambitoventa = 1&& _mes = 11,
            CALCULATE ( [Ventas Totales], DATESBETWEEN ( Fechas[Date], _d1, _d12 ) ),
    _any = _Year&& _ambitoventa = 1&& _mes = 12,
            CALCULATE ( [Ventas Totales], DATESBETWEEN ( Fechas[Date], _d1, _d13 ) )
    )