Showing results for 
Search instead for 
Did you mean: 
irene_m Frequent Visitor
Frequent Visitor

Please help - calculations, date ranges, and display problems

Hi all,

I'm hoping someone can help me figure out how to accomplish this being new to Power BI.  I'm more a MS-SQL and Crystal Reports, Excel person so this has stumped me.


I'm including two screenshots below, the first is what I want to accomplish and the second is where I'm at.  Right now I'm using a matrix table for my visualization.


Most of the data is imported and then I created three measures (PREVIOUS MONTH, BEFORE PRIOR MONTH, and GROSS CHARGES).    These are calculated as follows:



The data is filtered for a date range of 6/1/16-7/1/17.  Here are some of the problems I'm having.

  1. First of all, Power BI won't include the months that have no data.  Every time I turn on the option to Show items with no data, it returns dates from the 1800's.    I need it to just display all months that are in the date range selected, regardless of whether it has data or not.
  2. Second, I can't get a Grand Total (as a column) for the PREVIOUS MONTH and BEFORE PRIOR MONTH measures.  I created the GROSS CHARGES measure so I can display it as a ROW TOTAL of the three rows because Power BI won't add them up automatically.    As you can see in the second screenshot, the Total column is not correct for the PREVIOUS MONTH and BEFORE PRIOR MONTH rows.
  3. I also need to add Percent of Total column at the end of the Total but I don't know how to set it as a column.


Anyway, I'm really hoping someone can steer me towards the right direction.  


Thank you in advance for your help.


Final Product.pngWhere I'm At.png

Super User
Super User

Re: Please help - calculations, date ranges, and display problems



The figures for in the Grand Total column for the "Previous Month" and "Before Previous month" measures should be blank.  These measures make sense only for a particular month (not multiple months).  In PBI desktop, i dont think you will get an answer to question 3.  In Excel, question 3 can be answered by writing CUBE functions.

irene_m Frequent Visitor
Frequent Visitor

Re: Please help - calculations, date ranges, and display problems

Hi Ashish,

Thank you for replying.


Is there no work around to having Power BI give me totals (as a column) to all three then?  Is this the same problem as #3?  



Super User
Super User

Re: Please help - calculations, date ranges, and display problems



I am not sure of what you mean by your first sentence.  Question 3 can be solved by writing CUBE functions in Excel.

Helpful resources

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 155 members 1,841 guests
Please welcome our newest community members: