Forum Discussion

JoZu's avatar
JoZu
Frequent Visitor
4 years ago
Solved

Summarizing Accounts Receivable Data to show Net Balance Value per Invoice ID

Hi everyone First of all I'd like to mention, that I am rather new to PowerBI and I am reading a LOT (!) in this forum. The posts are very helpful and solved already numerous issues I faced. Howev...
Need Help
  • tamerj1's avatar
    tamerj1
    4 years ago

    HI JoZu 
    Here is a sample file with the solution https://we.tl/t-BJ0BtLRpwW

    Due Transation Currency = 
SUMX ( 
    SUMMARIZE ( 'Accounts Receivable', 'Accounts Receivable'[Customer Invoice ID],'Accounts Receivable'[Due Date Calendar Date] ),
    CALCULATE (
        VAR CurrentDueDate = SELECTEDVALUE ( 'Accounts Receivable'[Due Date Calendar Date] )
        RETURN 
            IF (
                CurrentDueDate <= TODAY ( ) && CurrentDueDate <> BLANK ( ),
                CALCULATE ( 
                    SUM ( 'Accounts Receivable'[Balance in Transaction Currency] ), 
                    ALLEXCEPT ( 'Accounts Receivable', 'Accounts Receivable'[Customer Invoice ID] ) 
                )
            )
    )
)

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

HI JoZu 
Here is a sample file with the solution https://we.tl/t-BJ0BtLRpwW

Due Transation Currency = 
SUMX ( 
    SUMMARIZE ( 'Accounts Receivable', 'Accounts Receivable'[Customer Invoice ID],'Accounts Receivable'[Due Date Calendar Date] ),
    CALCULATE (
        VAR CurrentDueDate = SELECTEDVALUE ( 'Accounts Receivable'[Due Date Calendar Date] )
        RETURN 
            IF (
                CurrentDueDate <= TODAY ( ) && CurrentDueDate <> BLANK ( ),
                CALCULATE ( 
                    SUM ( 'Accounts Receivable'[Balance in Transaction Currency] ), 
                    ALLEXCEPT ( 'Accounts Receivable', 'Accounts Receivable'[Customer Invoice ID] ) 
                )
            )
    )
)