=calculate( sum(TB[Sales]) , DATESYTD(Calendar[Date]))

[lovallee]

Good day,

In PowerPivot for Excel 2013:
Table "TB" has 3 fields: Date, Region, Sales
Table "Calendar" has only one field: Date (which list all dates for many years)
Then I created Calculated YTDSales=calculate( sum(TB[Sales]) , DATESYTD(Calendar[Date]))

My PowerPivot as as follow (a trended view by date of cumulative YTD sales):

YTDSales Date
Region Jan Feb Mar
East 1 * 2
West 1 2 3

* Empty cell
_____________________

Issue:
East made no sales in Feb. Cumulative sales are therefore 1. But the pivot table returns an empty cell simply because there are no sales in Feb!

Question:
How can I force the pivot table to show YTD sales in months in which there are no sales?

Thanks!

 

[one3nine0]

I just replicated your calculated field and it worked fine.

East 1 1 2
West 1 2 3

Assuming that west had a sale in each month. and east only had a sale in Jan and Mar

Are you sure your relationships are working? Or is there something else missing (ie another table or column involved?)

 

[lovallee]

I figured it ...
in the colum filter, I must place the Date field from the "Calendar" table...

Thanks one3nine0