Forum Discussion

enocperez's avatar
enocperez
Frequent Visitor
10 years ago

Calculates MTD with New Measure

I want to get a New Measure and I get an errros all the time what am I doing.   Sales MTD =CALCULATE( [Sales], PARALLELPERIOD('Item Sales'[Date Of Business], -1,MONTH))  
MattAllington's avatar
MattAllington
Icon for Community Champion rankCommunity Champion
10 years ago

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's avatar
enocperez
Frequent Visitor
10 years ago

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

 

 

 

  • Sean's avatar
    Sean
    Icon for Community Champion rankCommunity Champion
    10 years ago

    enocperez

    Do you have a Date table?

     

    MTD = TOTALMTD([MeasureName], CalendarTable[Date])
    MTD2 = Calculate([MeasureName],DATESMTD(CalendarTable[Date]))

     

    You can also do YTD and QTD with these just change where it says MTD to either one...

     

    Let me know if they work for you.

  • MattAllington's avatar
    MattAllington
    Icon for Community Champion rankCommunity Champion
    10 years ago

    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. 

     

    Also, by the way, the PARALLELPERIOD looks wrong too.  I would first write current MTD using TOTALMTD

    Total Sales MTD = TOTALMTD(sum('item sales'[sales]),calendar[date])

    and then a version for MTD prior period like this 

    =calculate([Total Sales MTD],datesadd(calendar[date],-1,month))

     

    as Sean said, you also need a calendar table.  You can read about that in my knowledge base http://exceleratorbi.com.au/power-pivot-calendar-tables/

  • v-sihou-msft's avatar
    v-sihou-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    10 years ago

    Hi enocperez,

     

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

     

    MTD:=CALCULATE(SUM('Item Sales'[Sales]), DATESMTD('Date Table'[FullDate]))

     

    Reference:

    DATESMTD Function (DAX)

     

    Regards,