# Work Order Aging in Powerpivot



## butlerrbrian (Jan 30, 2013)

I am attempting to complete a report of work orders aging by days in 9 different buckets for columns, with multiple "customers" in rows. In Excel, I would use =COUNTIF with a nested AND to accomplish this, but I am struggling with it in Powerpivot. There is a different table for each "customer", but the measures are the same.


----------



## powerpivotpro (Jan 30, 2013)

Try a CALCULATE(COUNTROWS(...), _filter1 _<filter1>, _filter2_<filter2>) perhaps?

CALCULATE is am amazing function and your new best friend, even if my suggestion above is wrong.  See this post:

PowerPivot DAX: CALCULATE is a supercharged SUMIF « PowerPivotPro</filter2></filter1>


----------



## butlerrbrian (Jan 30, 2013)

Thanks Rob.  I had used CALCULATE with FILTER to obtain the 0-2 age in an unconscious violation of your rules for FILTER.  COUNTROWS makes much more sense, maybe with some nested ANDs for the various buckets.  I'll try it when in the office tomorrow and post back on the success rate.  Thoroughly enjoying your book and learning loads by building on the temperature mash up while reading.


----------



## powerpivotpro (Jan 30, 2013)

Cool, glad to hear it!

Note that each filter argument to CALCULATE is essentially/implicitly "ANDed" together with the others, so I don't think you will need the AND() function explicitly.


----------



## butlerrbrian (Feb 1, 2013)

OK.  So I figure this will the be syntax for my measure, but it blows up on COUNTROWS because the value of [Aging Days] cannot be determined in the current context.

Thoughts?
 =CALCULATE(COUNTROWS('Open Orders'[Aging Days]), 'Open Orders'[Aging Days]<3, 
('Open Orders'[Aging Days]<5)-('Open Orders'[Aging Days]<3), 
('Open Orders'[Aging Days]<7)-('Open Orders'[Aging Days]<5), 
('Open Orders'[Aging Days]<11)-('Open Orders'[Aging Days]<7), 
('Open Orders'[Aging Days]<16)-('Open Orders'[Aging Days]<11), 
('Open Orders'[Aging Days]<21)-('Open Orders'[Aging Days]<16), 
('Open Orders'[Aging Days]<26)-('Open Orders'[Aging Days]<21), 
('Open Orders'[Aging Days]<31)-('Open Orders'[Aging Days]<26), 
'Open Orders'[Aging Days]>=31
)


----------



## powerpivotpro (Feb 1, 2013)

Well there might be a couple of problems.  First, COUNTROWS does not expect a column as the first argument.  Just give it COUNTROWS('Open Orders') to start with.

Second, I don't understand what you semantically intend a filter argument like "('Open Orders'[Aging Days]<5)-('Open Orders'[Aging Days]<3)" to do.

I am unfamiliar with using a minus sign there.  It's also hard for me to imagine what rows would meet all of those filter arguments simultaneously.  How can a row have an aging days value <5 AND >=31 for instance?  Seems like you'd get no rows.  I'm a long ways off from understanding your intent from all of those filter arguments.


----------



## butlerrbrian (Feb 1, 2013)

I actually realized the column problem in COUNTROWS about five minutes after posting - thank you Chapter 7.

Shortly thereafter, I had a moment of clarity and defined separate measures for each bucket, subtracting the next "younger" to display only orders in the desired range. CALCULATE(COUNTROWS(OpenOrders), ('OpenOrders'[AgingDays]<7)-('OpenOrders'[AgingDays]<5))


The product:



Row LabelsZero to 2 Days3 to 4 Days5 to 6 Days7 to 10 Days11 to 15 Days16 to 20 Days21 to 25 Days26 to 30 DaysOver 30 Days10856110202919102319534598181456Grand Total106192847331529

<COLGROUP><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width=92><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" width=94><COL style="WIDTH: 54pt; mso-width-source: userset; mso-width-alt: 2633" span=2 width=72><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" span=5 width=86><TBODY>

</TBODY>


----------



## butlerrbrian (Feb 1, 2013)

Now, I see I can do it this way too:  CALCULATE(COUNTROWS(OpenOrders), ('OpenOrders'[AgingDays]<7),('OpenOrders'[AgingDays]>=5)).


----------



## butlerrbrian (Feb 6, 2013)

Now for a really fun part... the underlying data is refreshed hourly from the web platform, and I have edited the connection to refresh hourly.

I'd like to capture the date/timestamp of the last refresh to include in a cell on the report.  Does Powerpivot have built-in Doument Properties like Excel with this information?


----------



## XLBob (Feb 6, 2013)

No I do not think that PowerPivot has a built-in Properties to capture report refresh time. You can add below custom function to do it. Hope it will work for you

Function LastRefreshTime(Rng As Range) As Variant</SPAN>
Dim PT As PivotTable</SPAN>
Set PT = Rng.Cells.PivotTable</SPAN>
LastRefreshTime = PT.RefreshDate</SPAN>

End Function</SPAN>
</SPAN>
</SPAN>
</SPAN>
</SPAN>
</SPAN>


----------



## butlerrbrian (Jan 30, 2013)

I am attempting to complete a report of work orders aging by days in 9 different buckets for columns, with multiple "customers" in rows. In Excel, I would use =COUNTIF with a nested AND to accomplish this, but I am struggling with it in Powerpivot. There is a different table for each "customer", but the measures are the same.


----------



## butlerrbrian (Feb 6, 2013)

I appreciate the quick reply, however - the function merely returns #VALUE.


----------



## powerpivotpro (Feb 6, 2013)

How about a table in PowerPivot with an =NOW() formula?  Then you add that as a pivot field or cube formula.  That might get updated even when you click a slicer but I don't think so.


----------



## XLBob (Feb 7, 2013)

you need to refrence a cell in pivottable. Note do not use mouse to select cause it will nest a getpivottable function inside your formular. use keyboard to enter a cell refrence.
then add customer format dd-mmm-yyyy hh:mm to your rusult. It will work. I use this a lot.


----------



## butlerrbrian (Feb 7, 2013)

Definitely works with cell reference, but changes every time the pivot refreshes, not the underlying data.  Maybe the right question is how to get the data.csv timestamp in a cell?


----------



## XLBob (Feb 7, 2013)

You need a trigger to let excel know to retrieve new data from your source. Have you tried to configure the connection property of your PivotTable.

</SPAN>


----------



## butlerrbrian (Feb 7, 2013)

The connection is configured to refresh all tables every 60 minutes.  I want to display the data.csv (not last sliced at) timestamp so that my users will know they have the latest data.  It also allows me to audit how reliable the data source is.


----------

