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 10 hours 53 minutes ago

I believe I understand what you are going for, but could you provide same sample data and an explanation of your two cases, where -1 is updated and where -1 is not updated. This will help me make sure that anything that is actually helpful.