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.
- Sumx(<table>, <expression>)
- Filter(<table>, <filter>)
- Event[ID#] - this is simply Counta(Event[ID])