Forum Discussion

lcasey's avatar
lcasey
Icon for Post Prodigy rankPost Prodigy
9 years ago

Use a calendar table for Invoices and Payments

Hello,   I cant beleive I have not found a solution on the web for this extreemly common request. I am hoping someone in here may have a solution.   I have 3 tables.  A calendar table an Invoice ...
Need Help
lcasey's avatar
lcasey
Icon for Post Prodigy rankPost Prodigy
9 years ago

I think I found a measure that may work: 

 

Balance Due = CALCULATE (
   [Balance],
    FILTER (
        ALL ( '00-Bad Debt - Payments'[DATE1]), MAX ( '00-Bad Debt - Invoices'[DOCDATE] )
    )
)

 

 

 

****Note*****

 

The "Balance"  Measure used in the calculate  is a simple formula of the difference between all invoices and all payments.

 

Balance = SUM('00-Bad Debt - Invoices'[SLSAMNT]) - Sum('00-Bad Debt - Payments'[APPTOAMT])

v-ljerr-msft's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi lcasey,

 

Could you try using the formula below to create a measure to see if it works in your scenario? :smileyhappy:

Balance =
SUM ( '00-Bad Debt - Invoices'[SLSAMNT] )
    - CALCULATE (
        SUM ( '00-Bad Debt - Payments'[APPTOAMT] ),
        FILTER (
            ALL ( '00-Bad Debt - Payments' ),
            '00-Bad Debt - Payments'[DATE1] IN VALUES ( Calendar[Date] )
        )
    )

Regards

  • lcasey's avatar
    lcasey
    Icon for Post Prodigy rankPost Prodigy
    9 years ago

    Hello,

     

    Unfortunatly, that didnt work. The Balances are all wrong.    I used your formula in the BalanceTest measure.