How to show previous period sales of items that didn't have sales in current month in Power Pivot?

Question

When displaying prior month sales, I noticed that items with no sales in current month are missing in the Pivot Table output of the data model. However, the grand total sales includes sales of those missing items.

I created a small data model to replicate the issue: 2 tables (1 sales table and 1 calendar table) and it has 1 single DAX formula:

Previous Month Sales:=CALCULATE(sum([Sales]),PREVIOUSMONTH(Sales[Date]))

In the output table, I would have expected 1 more item row for Potatoes, with 0 sales in current month and 31 in previous month.

Is there a way to force-show Potatoes item in the pivot table above when selecting date 24/03/2019? Can this be achieved with DAX formulas?

Answer 1

You need to use the Date field from your Calendar (Date) table, for Time Intelligence to work as you intend:

So change your Previous Month Sales measure to:

Sales Previous Month:=CALCULATE ( 
        SUM ( Sales[Sales] ),
        PREVIOUSMONTH ( Calendar[Date] )
    )

I'd also recommend creating an Explicit Measure for Sales:

Sales Total:=SUM ( Sales[Sales] )

Now you can recreate your pivot output, using Date field(s) from Calendar in your slicer / filter, and you should get the output you require: