Help making FinanceDate

[#1]

Hi all

I have some trouble getting the right FinanceDate to invoices

I have invoices with 2 dates

- InvoiceDate

- InsertDate (the date the invoice has been inserted)

I have a table with date for FinanceMonthCloseDate with

- FinanceMonth

- FinanceMonthCloseDate

What I want is to get a FinanceDate, that matches the FinanceMonth

If Invoice Month = Insert Month then InsertDate

if Invoice Month < Insert Month and InsertDate <= FinanceMonthCloseDate then InvoiceDate

But if FinanceMonthCloseDate  is in a FinanceMonth that have another FinanceMonthCloseDate  than Insertdate, then the FinanceDate should be 01 in FinanceCloseMonth that match the Insertdate(Month)

Attached file with sample data

My problem is the Invoice in file with "yellow" (no 10), when I make the FinanceDate I can't get it right, the result I would like is 29-09-2014 (InsertDate)

Hope the question made sence

Regards

NJ

Meddelelse blev redigeret af: Niels Juel Attached qvw file with sample

[#2]

Hi,

could you please attached an example?

[#3]

Hi

Example attached

[#4]

you should try your condition in pivot table

[#5]

Hi

why should I do that ?

The FinanceDate is to be used with other FinanceDates in the full QV as the Date dimension. This is just an example of what I need in this case.