cumulative sum with multiple conditions

[bvandenbossche]

Hi, 

I have a table with a list of invoices, with the invoice date, payment status, amount and if payment status = true also a payment date. Below is a small sample of the dataset:

Now I would like a table with one row per month, which gives for each month the cumulative amount invoiced and the unpaid balance. For the unpaid balance I want to take into account the invoices that are still unpaid today (payment status = false) as well as the invoices that were not yet paid at the end of the period in the current row (so payment date > last day of the month for this row). 

The cumulative amount works fine with: RangeSum(Above(Sum([Amount incl VAT]), 0, RowNo())). I'm struggling with the unpaid amount however, this doesn't seem to work: 

RangeSum(Above(Sum(

     {$<[Payment Status]={'False'}>+

     $<[Payment Date.Calendar.Date]={">$( monthend(max([Invoice Date.Calendar.Date])) )"}>}

     [Amount incl VAT]), 0, RowNo()

))

Any ideas on how to get this to work are highly appreciated! 🙂

Best regards,

Bart

[Anil_Babu_Samineni]

With in the object, Can you add [Payment Date.Calendar.Date] and [Payment Status] as dimension and see?

[bartvandenbossche]

Hi, I'm not sure I understand your suggestion. If I add those 2 dimensions, I don't get a total per month anymore? Also, it has no effect on the outcome of my expression: 

[sunny_talwar]

May be you need this

RangeSum(Above(

  Sum({$<[Payment Status] = {'False'}, [Payment Date.Calendar.Date] = {">$(=MonthEnd(Max([Invoice Date.Calendar.Date])))"}>} [Amount incl VAT])

, 0, RowNo()))

[bartvandenbossche]

Hi, thanks for the suggestion, but that only returns 0's apparently...

[sunny_talwar]

Would you be able to share a sample to check this out?

[bartvandenbossche]

Sure, here is some sample data for 3 months.