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