Forum Discussion

reynaldo_malave's avatar
reynaldo_malave
Icon for Helper III rankHelper III
5 years ago

SUMX with default date

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

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      reynaldo_malave
      Icon for Helper III rankHelper III
      5 years ago

      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