Flag Column to Exclude Records

[alexricker0928]

Hello PBI Super Users!

I have a report that ultimately ages open balances of customer accounts based on due dates. The goal is to show the balance by division, customer account, and voucher (hence the hierarchy in the matrix). The report looks like this at a glance:

Each customer has a 'Balance as Of Date', this is the balance of that customer as of the date in the date filter. Then, each of those balances is aged based on the due date of the voucher into buckets ('Future','1-30 days','31-60 days' etc.). All the values of the buckets in each row should add up to the 'Balance As of Date' in that row. 

The 'Balance As of Date' column calculates correctly for all customer and vouchers.

The problem I'm having comes when I have to age those amounts. For example, customer 040033, in the screenshot above. The 'Balance As of Date' is 0 for that customer because all four vouchers happen to add up to zero, which is correct. However, each voucher is aged based on the due date (all vouchers could have different due dates). Because voucher 'GLJE-000057269' has a different due date than the other three, it's being aged into a different bucket. Because of that, the totals for that customer are off. In reality, there should be no amounts aged for that customer because the 'Balance As Of Date' is 0. 

I'm here in this chat because I want to add a flag that says "If the 'Balance As Of Date' for the customer in total is 0, then flag each voucher with 1, otherwise 0). It would look like this in the end.

This way, I could put a visual level filter that said 'flag is not 1' to exclude the records.

I tried this in my flag formula, but it's not working as expected.

flag =

VAR summarized = SUMMARIZE(OpenAR,OpenAR[Division],OpenAR[ACCOUNTNUM],"Balance As Of Date",[Balance As Of Date])

Return

IF(SUMX(summarized,[Balance As Of Date])=0,1,0)

Any help here is much appreciated!!

Here is a link to the file: 
https://drive.google.com/file/d/1SfKu_R1oNLdU_4WanElWqho9RJnIvzJG/view?usp=sharing

[AlB · Accepted solution]

Hi @alexricker0928 

flag V2 =
IF (
    NOT ISBLANK ( SELECTEDVALUE ( OpenAR[VOUCHER] ) ),
    IF (
        ROUND ( CALCULATE ( [Balance As Of Date], ALL ( OpenAR[VOUCHER] ) ), 5 ) = 0,
        1,
        0
    )
)

 Note I had to use ROUND( ) to 5 decimals because the result at the customer level was very small but not zero (10^-12 or stg like that) so the =0 would return FALSE otherwise

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[alexricker0928]

@AlB , yes this did the trick. Thank you very much for your help!