Calculation sum depending on the date difference

[v_n]

Hello everybody,

I am currently working on an application that will show me the total open items of our customers.

For this purpose, there are various time intervals of overdue, e.g. less than 0 days, 1 to 30 days, etc. I calculate this with the due date and a reference date:

= if(
Interval([reference_date]-[due_date],'d') <= 0, Sum([Amount])
)

= if(
Interval([reference_date]-[due_date],'d') <= 0 and Interval([reference_date]-[due_date],'d') <= 30, Sum([Amount])
)

This works as long as I have the due date as a dimension in my table.

As soon as I remove the due date from the table, nothing is calculated anymore.

How do I make the calculation work in aggregated form?

[sunny_talwar · Accepted solution]

Try this may be

=Sum(If(Interval([reference_date]-[due_date], 'd') <= 0, [Amount]))

= Sum(If(Interval([reference_date]-[due_date], 'd') > 0 and Interval([reference_date]-[due_date], 'd') <= 30, [Amount]))

But a more efficient way to do this would be to create a new field in the script

Interval([reference_date]-[due_date], 'd') as duration

and then use that in your set analysis

=Sum({<duration = {"<=0"}>}[Amount])

=Sum({<duration = {">0<=30"}>}[Amount])

[v_n]

Thank you! It works. 

The more efficient way does not work for me because I want to select the reference date in a filter within my app.

But the other solution is fine.