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: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

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
484 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
422 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
415 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
432 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
426 Views
0
Syndicate_Admin
Administrator
Administrator

‎09-01-2023 03:36 AM

Calculate only 2022

Message 5 of 10
441 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
439 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
454 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
447 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
457 Views
0

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

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

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

View All
Top Solution Authors
View All