locked
Countrows, filter and calculated columns RRS feed

  • 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 the_txeriff Thursday, December 13, 2012 1:58 PM
    Thursday, December 13, 2012 12:37 PM
    Answerer

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! :)

    Some Random Thoughts

    Follow me on Twitter

    Thursday, December 13, 2012 12:09 PM
    Answerer
  • 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 the_txeriff Thursday, December 13, 2012 1:58 PM
    Thursday, December 13, 2012 12:37 PM
    Answerer
  • omg... yep you are right.

    I feel stupid.

    Thursday, December 13, 2012 1:58 PM