Forum Discussion

bzjens's avatar
bzjens
Frequent Visitor
4 years ago

Dynamic Receivables aging

Hi,

 

I have spent a few days trying to find a solution here, and I cannot find a post in here that sets me in the right direction.

 

I have a list of invoices from our accounting system with the following columns and a date slicer:

(Invoice table and date table not related)

 

The above image looks correct, and I have been using this formula for the "amountRemaining" measure:

 

 

amountRemaining = IF(MIN(Invoices[dueDate]) < MIN('Calendar'[Date]) &&
    MAX('Calendar'[Date]) > MIN(Invoices[datePaid]), 
    SUM(Invoices[invoiceAmount]) - SUM(Invoices[amountPaid]), SUM(Invoices[invoiceAmount]))

 

 

 

All good until I choose a date after one of the datePaid dates:

 

amountRemaining looks OK for the client that has made the payment (Client B), but the total is now wrong

It shows 300 000 - but it should be 530 000.

I need this total for a bar chart with date as x-axis.

 

Anyone knows how to fix this?

 

Thanks,

Børre

Need Help

3 Replies

  • TheoC's avatar
    TheoC
    Community Champion
    4 years ago

    Hi bzjens 

     

    Can you recreate the measure in the form of a Calculated Column then add that to the table?  This may lead to a quicker resolution. 

     

    Otherwise, you will likely need to create a secondary measure using the "IF ( HASONEFILTER (Measure) = TRUE() , Measure1 , Measure2)" philosophy as explained by Greg in this post: https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

     

    Hope the above is of some type of help 🙂

     

    Theo

    • bzjens's avatar
      bzjens
      Frequent Visitor
      4 years ago

      Hi TheoC 

       

      Thanks for replying!

      I think I will have trouble using calculated columns as I'm aggregating over rows based on dates in the slicer

      (To have my chart showing i.e. month by month)

       

      But Greg's post is likely the way to go.

      Thanks for sending me in that direction!

       

      Børre

      • TheoC's avatar
        TheoC
        Community Champion
        4 years ago

        bzjens absolutely mate. No trouble at all.

         

        If it's not exactly what you were after, you may be able to achieve the outcome you're wanting by adjusting your SUMs to SUMXs. For example:

         

        amountRemaining = IF(MIN(Invoices[dueDate]) < MIN('Calendar'[Date]) &&
    MAX('Calendar'[Date]) > MIN(Invoices[datePaid]), 
    SUMX('Invoices' , Invoices[invoiceAmount]) - SUMX('Invoices' , Invoices[amountPaid]), SUMX('Invoices',Invoices[invoiceAmount]))

         

        Let me know either way!

         

        Theo 🙂