Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more

Reply
Syndicate_Admin
Administrator
Administrator

Simplify DAX calculate amount by year and month

‎09-01-2023 01:25 AM

Hello

I have the following table where the Sales Import column is customized:

apenaranda_0-1693556129784.png

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)
))))))))))))))

As you can see, I am calculating the amount of sales for each year and month but I would like to know if there is a possibility of reducing the DAX code and that it is not so long. The code part is only 2023 but I actually want to put for 2022, 2023 and then with future years 2024 ...

Thank you.

Message 1 of 10
623 Views
0
9 REPLIES 9
Syndicate_Admin
Administrator
Administrator

‎09-01-2023 04:42 AM

I understand, but that only makes me calculate either 2022 or 2023 as you put it.

What I would like is for you to do just the whole journey with both 2022 and 2023, 2024 ... kind of a loop for

Message 9 of 10
561 Views
0
Ahmedx
Super User
Super User
In response to Syndicate_Admin

‎09-01-2023 05:39 AM 
with both 2022 and 2023, 2024 ... kind of a loop for

I don't think it can be done in a calculated column.

Message 10 of 10
554 Views
0
Syndicate_Admin
Administrator
Administrator

‎09-01-2023 04:03 AM

sorry, could you tell me what changes there are? It looks the same as the first one. I do not understand where he makes the route of 2022, then 2023 ...

Message 7 of 10
571 Views
0
Ahmedx
Super User
Super User
In response to Syndicate_Admin

‎09-01-2023 04:36 AM 
VAR _Year = 2022
VAR _Year = 2023
Message 8 of 10
565 Views
0
Syndicate_Admin
Administrator
Administrator

‎09-01-2023 03:36 AM

Calculate only 2022

Message 5 of 10
580 Views
0
Ahmedx
Super User
Super User
In response to Syndicate_Admin

‎09-01-2023 03:40 AM

try this

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 ) )
    )
Message 6 of 10
578 Views
0
Syndicate_Admin
Administrator
Administrator

‎09-01-2023 03:13 AM

Hello

It seems to work, the question is:

The example you give me, I see that it is for 2023

Could you do something in the part of the calculations to do from the year 2022 onwards?

Message 3 of 10
593 Views
0
Ahmedx
Super User
Super User
In response to Syndicate_Admin

‎09-01-2023 03:22 AM

try this

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 ) )
    )
Message 4 of 10
586 Views
0
Ahmedx
Super User
Super User

‎09-01-2023 02:34 AM

pls try this

 

 

 

 

importe ventas = 

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

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 ) )
    )

 

 

 

 

Message 2 of 10
596 Views
0

Helpful resources

Announcements
Power BI DataViz World Championships

Power BI Dataviz World Championships

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now!

November Power BI Update Carousel

Power BI Monthly Update - November 2025

Check out the November 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All