cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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:

  • PREVIOUS MONTH: = CALCULATE(sum(TABLE[CHARGE_AMOUNT]), PREVIOUSMONTH(DATE_DIMENSION[CALENDAR_DT]))
  • BEFORE PRIOR MONTH: =CALCULATE(sum(TALE[CHARGE_AMOUNT]), PARALLELPERIOD(DATE_DIMENSION[CALENDAR_DT],-2MONTH))
  • GROSS CHARGES: = TABLE[PREVIOUS MONTH] + [BEFORE PRIOR MONTH] + SUM(TABLE.[CHARGE_AMOUNT])

 

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

3 REPLIES 3
Super User
Super User

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

Hi,

 

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?  

 

Thanks

Super User
Super User

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

Hi,

 

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