Convert Excel formula to PowerPivot

F

frankee_gee

Board Regular
Joined
Mar 3, 2008
Messages
144
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Expert help needed, bad.


I would like help with converting the following formula from excel to PowerPivot. I use these calculations for aging reports.

=IF(([@ErectDate]<=DATE(2015,3,31))*(([@DismantleDate]="")+([@DismantleDate]>=DATE(2015,3,31))),LOOKUP(DATE(2015,3,31)-[@ErectDate],{0,90,121,181},{"Less than 90 days","90-120 days","120-180 days","> 180 days"}))

Andrew Poulsom helped create the original http://www.mrexcel.com/forum/excel-questions/817651-reporting-aging-using-3-fomulas-per-month.html



This formula works great in my excel table. I would like to create this as a calculated field in powerpivot.


Thank you for any help in advance,

Frankee :)
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
Try this:

Code: 
=IF(AND([ErectDate]<=DATE(2015,3,31),OR([DismantleDate]=BLANK(),[DismantleDate]>=DATE(2015,3,31))),SWITCH(
TRUE(),
INT(DATE(2015,3,31)-[ErectDate])<90,"Less than 90 days",
INT(DATE(2015,3,31)-[ErectDate])<121,"90-120 days",
INT(DATE(2015,3,31)-[ErectDate])<181,"121-180 days",
"> 180 days"
),
BLANK()
)
 
Upvote 0
Hello Tim,

This worked to perfection. I would have never figured this out.

Appreciate the help. This is an aid for end of month aging reports. I have to add this as a column going back to Jan 2014. By the end of this year I will have a total of 24 column, yikes. Would you have any ideas as to how I could avoid having to create this for every month?

Thank you for the help,

Frankee Gee.
 
Upvote 0
Why do you add it as a new column, add a date column and a calculation for that date, just 2 columns.
 
Upvote 0
I agree with theBardd. I would add one Calculated Column with the month in the Power Pivot window, then add the code from above as a Calculated Field (called Measures in Excel 2010) which can be done on the POWERPIVOT ribbon in Excel.

It takes some getting used to, but Calculated Fields are the real "power" behind Power Pivot.

Click here for an introduction to Calculated Fields. This is an example from Excel 2010 so they are referred to as Measures, but Calculated Fields and Measures are the same thing.
 
Upvote 0
Guys Thanks for the help,

I'm still stuck. So I've done this in manually.

Each are made up of 5 Pivot Tables, I take the totals of each and paste in Row 24 - 29

And that's how I come up with my charts....where each month has stacked columns. each stacked column is made up of the four (4) aging categories.

I tried but got nowhere. Gotto be an easier way.


Excel 2012
ABCDEFGHIJKLM
Aging CountAging Spend
Jan2015Jan2015
Less than 90 days90-120 days121-180 days> 180 daysGrand TotalLess than 90 days90-120 days121-180 days> 180 daysGrand Total
Count of Jan2015Monthly Rate
Feb2015Feb2015
Less than 90 days90-120 days121-180 days> 180 daysGrand TotalLess than 90 days90-120 days121-180 days> 180 daysGrand Total
Count of Feb2015Monthly Rate
Mar2015Mar2015
Less than 90 days90-120 days121-180 days> 180 daysGrand TotalLess than 90 days90-120 days121-180 days> 180 daysGrand Total
Count of Mar2015Monthly Rate
Apr2015Apr2015
Less than 90 days90-120 days121-180 days> 180 daysGrand TotalLess than 90 days90-120 days121-180 days> 180 daysGrand Total
Count of Apr2015Monthly Rate
May2015May2015
Less than 90 days90-120 days121-180 days> 180 daysGrand TotalLess than 90 days90-120 days121-180 days> 180 daysGrand Total
Count of May2015Monthly Rate

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]335[/TD]
[TD="align: right"]81[/TD]
[TD="align: right"]94[/TD]
[TD="align: right"]161[/TD]
[TD="align: right"]671[/TD]
[TD="align: right"][/TD]

[TD="align: right"]$19,739.51[/TD]
[TD="align: right"]$6,567.98[/TD]
[TD="align: right"]$4,758.53[/TD]
[TD="align: right"]$7,670.11[/TD]
[TD="align: right"]$38,736.13[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]311[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]106[/TD]
[TD="align: right"]166[/TD]
[TD="align: right"]632[/TD]
[TD="align: right"][/TD]

[TD="align: right"]$18,943.87[/TD]
[TD="align: right"]$5,249.17[/TD]
[TD="align: right"]$9,500.32[/TD]
[TD="align: right"]$9,219.46[/TD]
[TD="align: right"]$42,912.82[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]328[/TD]
[TD="align: right"]82[/TD]
[TD="align: right"]87[/TD]
[TD="align: right"]186[/TD]
[TD="align: right"]683[/TD]
[TD="align: right"][/TD]

[TD="align: right"]$18,524.05[/TD]
[TD="align: right"]$7,757.88[/TD]
[TD="align: right"]$11,611.99[/TD]
[TD="align: right"]$12,356.96[/TD]
[TD="align: right"]$50,250.88[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]425[/TD]
[TD="align: right"]79[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]216[/TD]
[TD="align: right"]824[/TD]
[TD="align: right"][/TD]

[TD="align: right"]$31,934.83[/TD]
[TD="align: right"]$7,114.23[/TD]
[TD="align: right"]$12,839.40[/TD]
[TD="align: right"]$18,719.78[/TD]
[TD="align: right"]$70,608.24[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]

[TD="align: right"]422[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]137[/TD]
[TD="align: right"]240[/TD]
[TD="align: right"]846[/TD]
[TD="align: right"][/TD]

[TD="align: right"]$33,068.69[/TD]
[TD="align: right"]$4,618.28[/TD]
[TD="align: right"]$14,521.32[/TD]
[TD="align: right"]$24,152.09[/TD]
[TD="align: right"]$76,360.38[/TD]

[TD="align: center"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="bgcolor: #FFEB9C, align: right"][/TD]
[TD="bgcolor: #FFEB9C"]Less than 90 days[/TD]
[TD="bgcolor: #FFEB9C"]90-120 days[/TD]
[TD="bgcolor: #FFEB9C"]121-180 days[/TD]
[TD="bgcolor: #FFEB9C"]> 180 days[/TD]
[TD="bgcolor: #FFEB9C"]Grand Total[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #C6EFCE, align: right"][/TD]
[TD="bgcolor: #C6EFCE"]Less than 90 days[/TD]
[TD="bgcolor: #C6EFCE"]90-120 days[/TD]
[TD="bgcolor: #C6EFCE"]121-180 days[/TD]
[TD="bgcolor: #C6EFCE"]> 180 days[/TD]
[TD="bgcolor: #C6EFCE"]Grand Total[/TD]

[TD="align: center"]25[/TD]
[TD="bgcolor: #FFEB9C"]Count of Jan2015[/TD]
[TD="bgcolor: #FFEB9C, align: right"]335[/TD]
[TD="bgcolor: #FFEB9C, align: right"]81[/TD]
[TD="bgcolor: #FFEB9C, align: right"]94[/TD]
[TD="bgcolor: #FFEB9C, align: right"]161[/TD]
[TD="bgcolor: #FFEB9C, align: right"]671[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #C6EFCE"]Count of Jan2015[/TD]
[TD="bgcolor: #C6EFCE, align: right"]$19,739.51[/TD]
[TD="bgcolor: #C6EFCE, align: right"]$6,567.98[/TD]
[TD="bgcolor: #C6EFCE, align: right"]$4,758.53[/TD]
[TD="bgcolor: #C6EFCE, align: right"]$7,670.11[/TD]
[TD="bgcolor: #C6EFCE, align: right"]$38,736.13[/TD]

[TD="align: center"]26[/TD]
[TD="bgcolor: #FFEB9C"]Count of Feb2015[/TD]
[TD="bgcolor: #FFEB9C, align: right"]311[/TD]
[TD="bgcolor: #FFEB9C, align: right"]49[/TD]
[TD="bgcolor: #FFEB9C, align: right"]106[/TD]
[TD="bgcolor: #FFEB9C, align: right"]166[/TD]
[TD="bgcolor: #FFEB9C, align: right"]632[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #C6EFCE"]Count of Feb2015[/TD]
[TD="bgcolor: #C6EFCE, align: right"]$18,943.87[/TD]
[TD="bgcolor: #C6EFCE, align: right"]$5,249.17[/TD]
[TD="bgcolor: #C6EFCE, align: right"]$9,500.32[/TD]
[TD="bgcolor: #C6EFCE, align: right"]$9,219.46[/TD]
[TD="bgcolor: #C6EFCE, align: right"]$42,912.82[/TD]

[TD="align: center"]27[/TD]
[TD="bgcolor: #FFEB9C"]Count of Mar2015[/TD]
[TD="bgcolor: #FFEB9C, align: right"]328[/TD]
[TD="bgcolor: #FFEB9C, align: right"]82[/TD]
[TD="bgcolor: #FFEB9C, align: right"]87[/TD]
[TD="bgcolor: #FFEB9C, align: right"]186[/TD]
[TD="bgcolor: #FFEB9C, align: right"]683[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #C6EFCE"]Count of Mar2015[/TD]
[TD="bgcolor: #C6EFCE, align: right"]$18,524.05[/TD]
[TD="bgcolor: #C6EFCE, align: right"]$7,757.88[/TD]
[TD="bgcolor: #C6EFCE, align: right"]$11,611.99[/TD]
[TD="bgcolor: #C6EFCE, align: right"]$12,356.96[/TD]
[TD="bgcolor: #C6EFCE, align: right"]$50,250.88[/TD]

[TD="align: center"]28[/TD]
[TD="bgcolor: #FFEB9C"]Count of Apr2015[/TD]
[TD="bgcolor: #FFEB9C, align: right"]425[/TD]
[TD="bgcolor: #FFEB9C, align: right"]79[/TD]
[TD="bgcolor: #FFEB9C, align: right"]104[/TD]
[TD="bgcolor: #FFEB9C, align: right"]216[/TD]
[TD="bgcolor: #FFEB9C, align: right"]824[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #C6EFCE"]Count of Apr2015[/TD]
[TD="bgcolor: #C6EFCE, align: right"]$31,934.83[/TD]
[TD="bgcolor: #C6EFCE, align: right"]$7,114.23[/TD]
[TD="bgcolor: #C6EFCE, align: right"]$12,839.40[/TD]
[TD="bgcolor: #C6EFCE, align: right"]$18,719.78[/TD]
[TD="bgcolor: #C6EFCE, align: right"]$70,608.24[/TD]

[TD="align: center"]29[/TD]
[TD="bgcolor: #FFEB9C"]Count of May2015[/TD]
[TD="bgcolor: #FFEB9C, align: right"]422[/TD]
[TD="bgcolor: #FFEB9C, align: right"]47[/TD]
[TD="bgcolor: #FFEB9C, align: right"]137[/TD]
[TD="bgcolor: #FFEB9C, align: right"]240[/TD]
[TD="bgcolor: #FFEB9C, align: right"]846[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #C6EFCE"]Count of May2015[/TD]
[TD="bgcolor: #C6EFCE, align: right"]$33,068.69[/TD]
[TD="bgcolor: #C6EFCE, align: right"]$4,618.28[/TD]
[TD="bgcolor: #C6EFCE, align: right"]$14,521.32[/TD]
[TD="bgcolor: #C6EFCE, align: right"]$24,152.09[/TD]
[TD="bgcolor: #C6EFCE, align: right"]$76,360.38[/TD]

</tbody>
Aging​
Formulas used in PowerPivot columns

This is the code for Jan2015 Column:
Code: 
=IF(AND([ErectDate]<=DATE(2015,1,31),OR([DismantleDate]=BLANK(),[DismantleDate]>=DATE(2015,1,31))),SWITCH(
TRUE(),
INT(DATE(2015,1,31)-[ErectDate])<90,"Less than 90 days",
INT(DATE(2015,1,31)-[ErectDate])<121,"90-120 days",
INT(DATE(2015,1,31)-[ErectDate])<181,"121-180 days",
"> 180 days"
),
BLANK()
)


I use this code to show what I have today (at the moment)
Code: 
=IF(AND([ErectDate]<=TODAY(),OR([DismantleDate]=BLANK(),[DismantleDate]>=TODAY())),SWITCH(
TRUE(),
INT(Today()-[ErectDate])<90,"Less than 90 days",
INT(TODAY()-[ErectDate])<121,"90-120 days",
INT(TODAY()-[ErectDate])<181,"121-180 days",
"> 180 days"
),
BLANK()
)

Any help you can give would be appreciated.

Frankee
 
Upvote 0
Hey Scottsen - Yes, I have a Date Table built in and not using it for this calculated column at the moment because it has a date built-in to the formula. When I drop the calc column in this is what I get.


Excel 2012
PQRSTU
30Monthly RateJan2015
31MonthLess than 90 days90-120 days121-180 days> 180 daysGrand Total
32January$6,946.58$442.98$7,389.56
33February$359.06$359.06
34March$647.30$647.30
35April$323.37$323.37
36May$1,322.08$1,322.08
37June$920.68$920.68
38July$1,854.35$1,854.35
39August$1,549.35$343.76$1,893.11
40September$2,815.03$12.32$2,827.35
41October$6,291.14$394.15$593.06$7,278.35
42November$4,972.33$276.84$279.72$5,528.89
43December$7,820.60$126.84$7,947.44
44(blank)$444.59$444.59
45Grand Total$19,739.51$6,567.98$4,758.53$7,670.11$38,736.13
Aging


In reading all the links. Looks like i need Calculated field from the suggestions. What would this calculated field look like?


The thing is I need to have two things
1. Categoreis 1-4
2. Per month 2014 thru present May 2015. I can not wrap my head around how this field would work.


Thank you,

Frank.
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Solved
DAX measure that filters a table to a single row and returns text from a specified column (Excel365)
Replies
3
Views
293
Insert_Key
I
W
  • Question Question
In Power Query based on contract dates assign number of days per month
Replies
2
Views
447
Wishmaster40
W
I
  • Solved
DAX formula to convert time HH:MM:SS into decimal time mm.ss
2
Replies
10
Views
5K
IwannaBeLikeU
I
K
  • Solved
Excel Power Query: Custom Text String Formula
Replies
8
Views
2K
smozgur
smozgur
A
  • Solved
help required with complex (well, i think so) statement in power query
Replies
3
Views
296
ajm
A

Forum statistics

Threads
1,224,096
Messages
6,176,325
Members
452,721
Latest member
Du Toit

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top