Forum Discussion
Helper IIFind two different last date in the same column
Hello everyone.
I'm facing an issue and you may help me with that.
As you will see in the table below, I have four columns : invoice_number, invoice_date, customer_number, customer_machine_number.
The customer_machine number is a nullable column.
I notice that when I make a search the last date of the column invoice_date in this table, if the customer_number is not null and the customer_machine_number is null, I find one last date.
But if the customer_number and the customer_machine_number are both not null, I find another last date.
I would like to show the two different last date in a matrice table but I don't know how to do it.
Anyone ?
| invoice_number | customer_number | customer_machine_number | invoice_date |
| 7876875 | 123456 | 123678 | 1/1/2021 |
| 7786875 | 123456 | 1/1/2021 | |
| 7096875 | 123456 | 123678 | 1/1/2021 |
| 7456875 | 123456 | 1/1/2021 | |
| 7026875 | 123456 | 1/1/2021 | |
| 7946875 | 123456 | 123678 | 2/1/2021 |
| 7086875 | 123456 | 1/1/2021 | |
| 7879105 | 123456 | 123678 | 2/1/2021 |
| 7870375 | 123456 | 1/1/2021 | |
| 7870R15 | 123456 | 1/1/2021 | |
| 78015Z5 | 123456 | 123678 | 2/1/2021 |
| invoice_number | customer_number | customer_machine_number | last_date(invoice_date) |
| 78015Q5 | 123456 | 123678 | 2/1/2021 |
| invoice_number | customer_number | customer_machine_number | last_date(invoice_date) |
| 78015Q5 | 123456 | 1/1/2021 |
I finally solve the problem with this measure :
Date1 = CALCULATE( MAX([INVOICE_DATE]), FILTER(INVOICE_TABLE, AND(CUSTOMER_NUMBER <> BLANK(), MACHINE_NUMBER <> BLANK())))And for the second date, just do Date2 = MAX(INVOICE_DATE)
5 Replies
- mh2587
Super User
measure 1 = // where machine and customer number not null
CALCULATE(MIN(Table[Invoice Date]),AND('Table'[Customer_Number] <> BLANK(),'Table'[Machine_Number] <> BLANK())
measure 2 = MIN(Table[Invoice Date]) 
eric98I finally solve the problem with this measure :
Date1 = CALCULATE( MAX([INVOICE_DATE]), FILTER(INVOICE_TABLE, AND(CUSTOMER_NUMBER <> BLANK(), MACHINE_NUMBER <> BLANK())))And for the second date, just do Date2 = MAX(INVOICE_DATE)
