Work Order Aging in Powerpivot

[butlerrbrian]

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]

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]

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]

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]

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]

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]

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:

[TABLE="width: 632"]
<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>[TR]
[TD="width: 92"]
Row Labels[/TD]
[TD="width: 94"]Zero to 2 Days[/TD]
[TD="width: 72"]3 to 4 Days[/TD]
[TD="width: 72"]5 to 6 Days[/TD]
[TD="width: 79"]7 to 10 Days[/TD]
[TD="width: 86"]11 to 15 Days[/TD]
[TD="width: 86"]16 to 20 Days[/TD]
[TD="width: 86"]21 to 25 Days[/TD]
[TD="width: 86"]26 to 30 Days[/TD]
[TD="width: 86"]Over 30 Days[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]1085[/TD]
[TD="bgcolor: transparent, align: right"]61[/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent, align: right"]20[/TD]
[TD="bgcolor: transparent, align: right"]29[/TD]
[TD="bgcolor: transparent, align: right"]19[/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]1953[/TD]
[TD="bgcolor: transparent, align: right"]45[/TD]
[TD="bgcolor: transparent, align: right"]9[/TD]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: transparent, align: right"]18[/TD]
[TD="bgcolor: transparent, align: right"]14[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[/TR]
[TR]
[TD="class: xl63"]Grand Total[/TD]
[TD="align: right"]106[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[/TR]
</TBODY>[/TABLE]

 

[butlerrbrian]

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

 

[butlerrbrian]

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]

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>