This topic contains 1 reply, has 2 voices, and was last updated by 
-
Posts
-
Hello,
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?
If you have Excel 2013 or the professional plus version of Excel 2010, you should take a serious look at how Power Query can help you.
The problem you have needs a data transformation solution (Power Query); it should not be made into a Power Pivot problem.
The forum ‘Power Pivot’ is closed to new topics and replies.