PowerPivot use max date for MTD if no record exists

Question

I want to calculate the MTD sales of products, however some products aren't always sold everyday. Using the time intelligence DAX functions TOTALMTD ignores products if there are no sales for the selected date. e.g. if I have product A and B

Data:

Product InvoiceDate Sales
A   2016/12/01  1
B   2016/12/01  2
B   2016/12/02  3

What I want to show with MTD calc:

Product InvoiceDate 
A   2016/12/02  1
B   2016/12/02  5

What I currently get:

Product InvoiceDate 
B   2016/12/02  5

Answer 1

While I'm not sure how or if you can achieve this via a Pivot table, I was able to do this using a linked DAX query. To learn more about how you can run a linked DAX query, look here.

Before my formula, I created a calendar table in PowerPivot (Design Tab -> Date Table). You don't need to do this, but in order to have a fool-proof solution, you probably should. Create a calendar table on the side, then join it to your main table using the Date column.

Here's my DAX:

EVALUATE
SUMMARIZE(CROSSJOIN(VALUES(Table[Product]),VALUES(Calendar[Date])),[Product],[Date]
,"Sales",SUM(Table[Sales])
,"MTD sales",TOTALMTD(SUM(Table[Sales]),Calendar[Date])
)

Using this query I was able to achieve the following results:

I think that's exactly what you were looking for. The solution may be a bit tricky for a beginner, so let me know if I can clarify anything for you.