I need a filter that looks at the first 3 years of a table and exclude the rest.

[ThomasWeppler]

Hi power BI community

I need a filter that looks at the first three years in a table for each customer_id and include them and exclude the rest.
The relevant colums are the following.

Customer_id: A number that shows the id of each customer
Due_date: The date a invoice has been send.
Total_amount: A number of money that should be added togeter if they are in the first three years of the due_date for each customer.

So far I have this, but it doesn't seem to work.

First 3 years =

VAR MinDueDate = CALCULATE(MIN('2905 InvoiceLine'[Duedate]), ALLEXCEPT('2905 invoiceline', '2905 invoiceline'[customer_id]))

RETURN

    IF('2905 InvoiceLine'[Duedate] < DATEADD(min('2905 Invoice'[Due_date]), 3, YEAR), 1, 0)

Can anyone help me? All help will be greatly appreciated.

[v-kaiyue-msft · Accepted solution]

Hi @ThomasWeppler ,

Thanks to @_AAndrade  and @mh2587 for the reply, please allow me to provide another insight:

The dateadd function returns a table containing a column of dates starting from the date in the current context and moving forward or into the past by a specified number of intervals. It must be a date that already exists in the table. If there is no date three years after MinDueDate in your table, it will return a null value.

You can see this example. I have these dates in my table.

To get a date three years from now, it returns empty.

To get the date three days later, the data that already exists in the table is returned.

If you want to get the date three years later, you can use an expression similar to the following.

Measure =
DATE(YEAR(MAX('Table'[date]))+3,MONTH(MAX('Table'[date])),DAY(MAX('Table'[date])))

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mh2587]

First 3 years = //Try this and just modified the if condition
VAR MinDueDate = CALCULATE(MIN('2905 InvoiceLine'[Duedate]), ALLEXCEPT('2905 InvoiceLine', '2905 InvoiceLine'[customer_id]))
RETURN
    IF('2905 InvoiceLine'[Duedate] <= DATEADD(MinDueDate, 3, YEAR) && '2905 InvoiceLine'[Duedate] >= MinDueDate, 1, 0)

[ThomasWeppler]

It has a problem with dateadd. It will not add a variable.

[ThomasWeppler]

Sure here is some more info.
I have worked a bit more with it and I I have found the first date where an invoice was send. The only thing I need now is to add 3 years to that date.
I have tried with this DAX code to add a new column in my table.
DAX Code = DATEADD('2905 InvoiceLine'[First],3,YEAR)

It works on all the rows where the date is 06/01/2020 and one of the other rows. It seems very weird to me.
Can anyone help me advance from here?

[_AAndrade]

Probably you are getting that date because it's the correct output for all your data.

But if I well understood you need this logic for each invoice right?

If is the case you need to use a measure to do your calculations instead of a calculated column

[_AAndrade]

Hi @ThomasWeppler,

Can you provide an example of the current output and the expected output? Or a pbix file with same data so I can look at it?