How to show all dates in a powerpivot pivot table even when there is no data...

So, I've heard about this issue for a while now, but I never had to actually deal with it myself - until now, that is..

We have a system that records when a specific significant event occurs - we'll call this Event A.
Well, Event A doesn't always happen and frankly, we NEVER want it to happen.  So, when reporting month-to-month numbers, there are some times when a month shows "0" for the denominator and "0" for the numerator.  In a typical Excel pivot table, this isn't really a big deal you simply go into the properties and set the "Show items with no data on rows/columns" option to TRUE.

Unfortunately, with my particular power pivot model, it wasn't so simple.

My online investigation took me to this forum.  It was a lengthy conversation where multiple people offered the "easy" solution that ultimately didn't work.  However, there was one contributor that was spot on in understanding the issue.  It took me a while to pick through the conversation and get past the suggestions that still resulted errors to finally get at the solution I was looking for. So, I am going to attempt to explain what I did to save time:

The solution is this formula:

# of Event A:=sumx(filter(DimDateEvent,DimDateEvent[Months from Current]>0),0+Event[#ID])

I have two tables involved:

• DimDateEvent - Date dimension table that consists of only monthly data - I will only ever report Event A on a monthly basis so no need to get more granular that Monthly.
• Note - I have a column in my table to measure the number of months from current month.  The results are both positive and negative.  
• =(YEAR(TODAY())-YEAR([@[Beginning of Month]]))*12+MONTH(TODAY())-MONTH([@[Beginning of Month]])
• Event - My fact table that has the Event information.

Formulas Used in the Solution:

• Sumx(<table>, <expression>)

• Filter(<table>, <filter>)

Calculated Measure used in Solution:

• Event[ID#] - this is simply Counta(Event[ID])

Normally, when creating a pivot table, the source data is the table with your event information and you "Look up" the remaining details.  However, for this solution, we are going to use our DimDateEvent Table as the data and "Look Up" the event information.

So, lets walk through the formula:

=Sumx(

Sumx - allows you to apply an expression to the data being added.

=Sumx(Filter(DimDateEvent,DimDateEvent[Months from Current]>0)

Filter - provides the DimDateEvent table filtering it to show only last month and every month before it.

=Sumx(Filter(DimDateEvent,DimDateEvent[Months from Current]>0),0+Event[#ID])

I didn't want the table to show blanks, so I showed 0+ (this guarantees a value of "0") and then the result of the calculated measure Event[#ID] or in other words, the formula "Counta(Event[ID])".

So now we have completed the formula:

=sumx(filter(DimDateEvent,DimDateEvent[Months from Current]>0),0+Event[#ID])

Here is what it looks like in a pivot table: 

Note: No real data is being used here... The fiscal Year resets because I'm not showing the entire pivot table there is a Group A, Group B, Group C

No Demo File will be provided for this example.

Enjoy!

Show two labels on a single chart series

Just learned how to add a denominator at the base of a column on a column chart that shows an average (average is simply one example, but this could be used in multiple ways):

Note: I removed all identifying info from the image on purpose... 

Anyway, here's how to do it:

Create a chart for Average times and add the data lables above the columns.  

Now, add the second series that shows the count.  You will now have two Columns showing.  

Right-click the count series and set the overlap property to 100% and change the Count series' color to match the Average series' color.  The columns now overlap and look the same.  

Last, set the data label position for the count to show inside base. 

Anyway, thought I'd share.