balance with condition

[sraj]

Balance = SUM(vendorDTLPAY2_view[Amount (LCY)]) - SUM(vendorDTLPO2_view[Amount])​

Trying to add a condition to the measure above.  Need this field "vendorDTLPAY2_VIEW[AMOUNT (LCY)]" to pick only values that are less than 0.00.

Please advise.

Thanks!

[az38]

Hi @sraj 

try

Balance = CALCULATE(SUM(vendorDTLPAY2_view[Amount (LCY)]),vendorDTLPAY2_view[Amount (LCY)]<0) - SUM(vendorDTLPO2_view[Amount])​

do not hesitate to give a kudo to useful posts and mark solutions as solution

[sraj]

Hi AZ38,

Thanks for responding!! 

I seeing this message "TO USE SPECIAL CHARACTERS IN A MEASURE NAME, ENCLOSE THE ENTIRE NAME IN BRACKETS ([]) AND ADD A ] TO ANY CLOSING BRACKETS IN THE NAME" , I am trying to copy and paste what you have but it just giving me this message....am I missing something?

[az38]

@sraj 

are you sure, you write down the sentence correct?

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

Instead of copy paste try to write measure by yourself so that you will not miss any syntax or parenthesis.

Thanks
Pravin

[sraj]

Yes, that's what i did and its working now...but the calcualtion is wrong.  This is what i am getting:

Amount (LCY) = ($276405.42)

Amount = $271555.14

Balance = -547961

[az38]

so, @sraj 

what do you expect as desired output if you want to take into calculation only values "vendorDTLPAY2_VIEW[AMOUNT (LCY)]" tthat are less than 0.00."

please give a data example and desired result

do not hesitate to give a kudo to useful posts and mark solutions as solution

[sraj]

The balance needs to be 4,850.28

[az38]

@sraj 

and what result returnted with your initial sentence?

Balance = SUM(vendorDTLPAY2_view[Amount (LCY)]) - SUM(vendorDTLPO2_view[Amount])​

do not hesitate to give a kudo to useful posts and mark solutions as solution

[sraj]

I was able to figure it out, I was using the wrong values.  So this is resolved, thanks for your help and support!!

Balance = CALCULATE(SUM(vendorDTLPAY2_view[Credit Amount (LCY)])- SUM(vendorDTLPO2_view[Amount]))

[az38]

@sraj 

glad to hear 🙂 good luck!

do not hesitate to give a kudo to useful posts and mark solutions as solution