Hello everybody,

I have a problem in a report using Power Pivot. I hope someone can help me :)

I need to update a report every month. The problem is that the information I need to show is related only to the last month, so it's not year to date. Because of that, I have to create formulas for each month and keep changing the columns created by these formulas every month when the database is updated, which is not practical.

To start thinking about updating automatically these columns, I added 2 calculated columns in my Power Pivot database with ormulas that understand the "Current Month" (July, for example), and the past months (June as "Current Month -1", May as "Current Month -2" etc).

Column "RelativeMonthOffset": =((12 * YEAR([MONTHCOMP])) +  MONTH([MONTHCOMP])) - ((12 * YEAR(TODAY())) +  MONTH(TODAY()))

Column "Relative Month": =IF([RelativeMonthOffset]=0 ; "Current Month" ; "Current Month " & IF([RelativeMonthOffset]>0; "+"; "") & [RelativeMonthOffset])

*MONTHCOMP: The column with the months

My next step is creating a condition that understands I need to show the updated data ("Relative Month -1" June), but also understands that if this information is not updated yet, it has to show the "Relative Month -2" (May).

I have tried to use the Calculate, But I don't know if it can be used.I didn't know how to create the syntax, for example:

CALCULATE(sum(2015[REVENUES]);2015[RelativeMonthOffset]="Current Month -1") || 2015[RelativeMonthOffset]="Current Month -2"))

In this case, I have tried to use OR ( || ), but it doesn't work because the formula doesn't understand that there's a "priority" of showing the "Current Month -1".

Anybody can help me with this issue?

Thank you in advance!

• Edited by Christiane Pereira Friday, July 03, 2015 8:13 PM

First, I think you should really have a separate date/calendar lookup table.

That said, I think your efforts to have calc columns "do the work" was a great idea, and I would extend that further.  It's not clear to me where you are heading with your last Revenue measure... its like "Revenue Last Month Unless It is Not Complete".  But the "completeness" should probably be captured in your calculated columns.   

If you need a "measure that shows the most recent completed month", I would add a calc column that is "Is Current Month" which is only TRUE for calendar dates in the current (and complete) month?

Hello,

I don't know if I can attach a file here. I created an example showing what I'm trying to say with both cases, where "Current Month -1" is updated and where "Current Month -1" is not updated. Can I send by e-mail?

Thank you!

Hello Scott,<o:p></o:p>

How I could create a separate calendar lookup table?<o:p></o:p>

I update my last revenue measure (Current Month -1) normally in the first or second week of my current month.<o:p></o:p>

About the "completeness", the information of the "Current Month -1" is always complete when I update the database, I don't update the information of June in June, for example. I update the information of June in July, the information of July in August etc. The logic I want in a formula would be: Show the Revenue "Current Month -1", if it's not available yet, show the Revenue "Current Month -2". <o:p></o:p>

Thank you for your support!

How I could create a separate calendar lookup table?<o:p></o:p>

Hi Christiane,

To build your date table you will need to create one column in excel that will have all your date values. You can do this by:

1. In the A1 cell enter name of the column "Date"
2. In the A2 cell enter earlies value of your date table, for example "01/01/2005". Note this format might be different for your computer locale. Make sure that Excel understands that this is date type cell
3. In the A3 cell enter forumula "=A2+1". This formula will create a value for next day of your date.
4. Select cell A3 and then select square dot at the bottom right corner of that cell and drag it down. This will copy formula to rows below and each new row will have date incremented by 1 day. Make sure you create rows for each date that exists is used in your PowerPivot application

How can I create Calendar Date/Time table for PowerPivot if I do not have any source for that

Regards,