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

1
votes

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]))

table relationships

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.

item row missing

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?

1
exceldaxpowerpivot

1 Answers

2
votes

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:

enter image description here