SUMX with default date

[reynaldo_malave]

Hi guys,

I am trying to get a sumx to return last month values if no filter is applied. The measure i created is as follows:

MTD Piezas = 

var latest_month =

IF(
    MONTH(
        TODAY()
    )=1,
    12,
    MONTH(
        TODAY()
    )-1
)

var lastyear =

IF(
    latest_month=12,
    YEAR(
        TODAY()
    )-1,
    YEAR(
        TODAY()
    )
)
return

IF(
    ISFILTERED(Calendario[Mes_Año]),
    SUMX(
        'Ventas Simi',
        'Ventas Simi'[cantidad] 
    ),
    SUMX(
        FILTER(
            'Ventas Simi',
            MONTH('Ventas Simi'[fecha]) = latest_month && YEAR('Ventas Simi'[fecha]) = lastyear
        ),
        'Ventas Simi'[cantidad]
    )
)

any help would be greatly appreciated.

Reynaldo

[amitchandak]

@reynaldo_malave , for last month you can use time intelligence with date table ?

example

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
this month =MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))
last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
previous month value = CALCULATE(sum(''Table''[total hours value]),previousmonth('Date'[Date]))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions



Appreciate your Kudos.

[reynaldo_malave]

@amitchandak  here is the thing. This is an upgrade version of a report already in production. In the production version i used sum for some calculations but I require a row base calculation (sumx) since i am measuring performance on each of the products. Now, i am not trying to calculate last month sales.

I am trying to use last month as a default month when no dates are selected by using lastest_month and lastyear variables in the measure.

Is this possible? I mean it worked on the older version I dont know why is not working here.

Thanks,

Reynaldo