Matching different date fields in the same table

[caio_caminoski]

Hi guys! I have a weird question.

I have been working in an application for a while. Now, I was required to create an object that seems to be a bit tricky. It mixes the payment dates with the expire dates of the invoices. What I mean is that I have a date as dimension (expire date) as it follows

The problem comes when I try to write the expression for the third row (the amount paid in advance), as I need to allocate the values according to the payment date, not the expiring date (dimension).

In my mind I would try to go with sth like pick and match. But I am not sure how to do that.

PS: I am looking for a solution that wouldn't change my data model. I understand that if I change my table and creat only one field for date and qualify it as expired date or payment date, I would be able to use set analisys in the expression to do so.

I am attaching a QVW with an example and further information

Thanks in advance!

[stigchel · Accepted solution]

It would be better to change the data, but you can use a synthetic dimension to do this in the front end. In the expressions you then compare either Expire Date or payment date.

See attached

[caio_caminoski]

I need to study your solution a little bit, but it seems to work beautifully!

Thank you very much, Piet!