Forum Discussion

azaterol's avatar
azaterol
Icon for Helper V rankHelper V
2 years ago

Show last bill date

Hello everyone,

I have the invoice date in one column and I now want the last invoice date from the respective customer to be displayed in a column next to it. 

 

I used this measure, but I always only receive the last invoice date, the same invoice date that was issued. But this is wrong.

 

last bill date = LASTNONBLANK('Date'[Date],'BillTable'[Bill_date])

 

Customer Bill number Bill_date last bill date
John 123 03/20/23 03/20/23
John 334 04/11/23 04/11/23
Doe 888 07/07/23 07/07/23

 

What I want:

 

Customer Bill number Bill_date last bill date
John 123 03/20/23 08/10/23
John 334 04/11/23 08/10/23
Doe 888 07/07/23 07/07/23

 

I hope you can help me out. Thank you, for response.

 

 

Need Help

2 Replies

  • DOLEARY85's avatar
    DOLEARY85
    Icon for Resident Rockstar rankResident Rockstar
    2 years ago

    Hi,

     

    The sample data provided appears to be incomplete but i think from what you've described you could do this if you have a uniqe customer ID.

     

    The below measure should work - it will look for the maximum date in the Bill_Date column by only looking at the Customer column using ALLEXCEPT. Just change the Customer field to the unique ID of each customer so that it isn't impacted by duplicate names.

     

    Measure = CALCULATE(max('BillTable'[Bill_date]),ALLEXCEPT('BillTable','BillTable'[Customer]))
     
    If I answered your question, please mark my post as solution, Appreciate your Kudos 👍