Where does DAX MTD calc go?

Question

The stack is SQL relational tables into SQL 2014 Tabular consumed by Excel 2010.

The Tabular model grain is one row per purchase order (PO) line item. Each row has a dollar value (item cost$) which is used for a sum measure (total cost$).

A Time Intelligence Date table is related so the sum of total costs$ for a year can be determined for example.

How best to implement a month to date aggregate? Should a DAX query against the model calculate the MTD on the fly as I pull the data in Excel? Or is there a way to implement directly into the model at the PO line item grain?

Answer 1

I personally would stick a calculated measure in the model itself using TOTALMTD() - you could always just use SQL and do it in the back end though. Calculated measures are generally pretty efficient - your model would have to be huge before you start seeing performance issues with them so I wouldn't worry too much