Countrows, filter and calculated columns

• Question

• Hi all,

I have the following calculation:

Due date count 31-60 Days:=COUNTROWS(FILTER(charges;[ChargeDaysCalc]>30||[ChargeDayscalc]<=60))

And chargedayscalc its a column:

=1,*(TODAY()-[Due Date])

"Due date" its a datetime column

but in all the calculations (61-90 days, 91-120 days....) returns same value, so its not working for some reason. I belive its because the chargeDaysCalc but it shows properly on each row of the table.

Any ideas?

thanks!

Thursday, December 13, 2012 9:04 AM

Answers

• the calculation is fine, u just have to use && (AND) instead of || (OR)

otherwise the expression is always true as every number is either >30 OR <60

- www.pmOne.com -

• Marked as answer by Thursday, December 13, 2012 1:58 PM
Thursday, December 13, 2012 12:37 PM

All replies

• Can you try wrapping a calculate around ChargeDaysCalc in the Due date count calculation? Something like shown below

`31-60 Days:=COUNTROWS(FILTER(charges;calculate([ChargeDaysCalc])>30|| calculate([ChargeDayscalc])<=60))`

I am guessing that the context is not being passed properly...

If this doesnt work, can you upload your excel sheet somewhere and then paste the link,  so that others can take a look?

Cheers,
Jason | www.SqlJason.com
P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

Thursday, December 13, 2012 12:09 PM
• Hi Jason,

thanks for your reply.

Thats what I was thinking before posting. I tried again and it shows me error, telling me that chargedayscalc cannont be found.

the thing is that its tabular model.

I just made a excel file with Adventureworks DW.

http://www5.zippyshare.com/v/7540507/file.html

Thursday, December 13, 2012 12:26 PM
• the calculation is fine, u just have to use && (AND) instead of || (OR)

otherwise the expression is always true as every number is either >30 OR <60

- www.pmOne.com -

• Marked as answer by Thursday, December 13, 2012 1:58 PM
Thursday, December 13, 2012 12:37 PM
• omg... yep you are right.

I feel stupid.

Thursday, December 13, 2012 1:58 PM