Calculates MTD with New Measure

Question

I want to get a New Measure and I get an errros all the time what am I doing.&nbsp;Sales MTD =CALCULATE( [Sales], PARALLELPERIOD('Item Sales'[Date Of Business], -1,MONTH))&nbsp;

mattallington · Answer

Sales is a column. You have not told Power Pivot how to aggregate the column. Also you should never refer to a column without specifying the table name. So replace [Sales] with sum(TableName[Sales])

replace TableName with the actual name of your table.

enocperez · Answer

Ok I have made the update and still I get an issues with the Calculation. &nbsp;&nbsp;&nbsp;&nbsp;

sean · Answer

enocperezDo you have a Date table?&nbsp;MTD = TOTALMTD([MeasureName], CalendarTable[Date])MTD2 = Calculate([MeasureName],DATESMTD(CalendarTable[Date]))&nbsp;You can also&nbsp;do YTD and QTD with these just change where it says MTD to either one...&nbsp;Let me know if they work for you.

mattallington · Answer

You need to read the error messages you get. They normally tell you what the problem is. Your error says there are too many arguments for the sum function. That is a hint that you have missed a round bracket after the square bracket in sum.&nbsp;&nbsp;Also, by the way, the PARALLELPERIOD looks wrong too. &nbsp;I would first write current MTD using TOTALMTDTotal Sales MTD = TOTALMTD(sum('item sales'[sales]),calendar[date])and then a version for MTD prior period like this&nbsp;=calculate([Total Sales MTD],datesadd(calendar[date],-1,month))&nbsp;as Sean said, you also need a calendar table. &nbsp;You can read about that in my knowledge base&nbsp;http://exceleratorbi.com.au/power-pivot-calendar-tables/

v-sihou-msft · Answer

Hi enocperez,
&nbsp;
In DAX, to calculate the MTD value, we can use DATESMTD() to assign a MTD dates row set in filter within CALCULATE() funciton. And we can only pass one column as argument in SUM() function. In this scenario, you just need to SUM the sales column with a MTD row set filter within CALCULATE():
&nbsp;
MTD:=CALCULATE(SUM('Item Sales'[Sales]), DATESMTD('Date Table'[FullDate]))
&nbsp;
Reference:
DATESMTD Function (DAX)
&nbsp;
Regards,
&nbsp;
&nbsp;

Forum Discussion

Calculates MTD with New Measure

5 Replies

Related content

DAX Calculated Measure

DAX Measure for Ranking the valid rows and calculating the average rating

Measure or Calculated Column

Measure to calculate

Measure/Model calculation support needed