Please help - calculations, date ranges, and display problems

[Anonymous]

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.

[Ashish_Mathur]

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.

[Anonymous]

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

[Ashish_Mathur]

Hi,

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