Forum Discussion

LostinDaxland's avatar
LostinDaxland
Regular Visitor
2 years ago
Solved

DAX Help! Return Latest Date, but stop after finding first value

Hi - I'm really hoping that I can get some help here. I've been banging my head against the wall a while trying to figure this out. Please go easy on me - I'm very new to DAX (or any kind of coding...).

 

I would like to get the Latest Invoice column to put the phrase "Latest Invoice" into a corresponding row when it's found that that row's invoice is the latest for a particular Project Number. The hitch here is that sometimes, there will be more than one invoice that occurs on the same date. The way that I've written this DAX is such that it will put "Last Invoice" in both cells, because it is looking at only the date and seeing what it considers to be the 'latest date' in both. What I'd like to happen is for the first uniquely identified Invoice ID to get the 'Latest Invoice' and the other (or the rest, when there are more than 2) to go without a tag. I'm guessing that I need to write a script that stores the Invoice ID when it finds the date to be the latest ... or something? 

Here's what I'm using so far:

 

Written DAX 

Latest Invoice = var maxvalue =
calculate(
    MAX(Trial[Invoice Date]),
    allexcept(Trial,Trial[Project Number]))
    RETURN
    if(Trial[Invoice Date]=maxvalue,"Latest Invoice")
 
Table: 

 

Thank you! I appreciate any help on this. 

Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    2 years ago

    LostinDaxland , Try using below DAX

     

    Latest Invoice =
    VAR maxDate =
    CALCULATE(
    MAX(Trial[Invoice Date]),
    ALLEXCEPT(Trial, Trial[Project Number])
    )
    VAR minInvoiceID =
    CALCULATE(
    MIN(Trial[Invoice ID]),
    ALLEXCEPT(Trial, Trial[Project Number]),
    Trial[Invoice Date] = maxDate
    )
    RETURN
    IF(
    Trial[Invoice Date] = maxDate && Trial[Invoice ID] = minInvoiceID,
    "Latest Invoice",
    BLANK()
    )

4 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    2 years ago

    LostinDaxland , Try using below DAX

     

    Latest Invoice =
    VAR maxDate =
    CALCULATE(
    MAX(Trial[Invoice Date]),
    ALLEXCEPT(Trial, Trial[Project Number])
    )
    VAR minInvoiceID =
    CALCULATE(
    MIN(Trial[Invoice ID]),
    ALLEXCEPT(Trial, Trial[Project Number]),
    Trial[Invoice Date] = maxDate
    )
    RETURN
    IF(
    Trial[Invoice Date] = maxDate && Trial[Invoice ID] = minInvoiceID,
    "Latest Invoice",
    BLANK()
    )

  • Ritaf1983's avatar
    Ritaf1983
    Icon for Super User rankSuper User
    2 years ago

    Hi LostinDaxland 
    You can create from the PQ index column for invoices inside the project using the linked method in the first step :

    https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query

    The result will look like this:

    And the add calculated column using DAX :

    last invoice =
    var
    max_index = calculate(max('Table'[Index]),ALLEXCEPT('Table','Table'[project]))
    Return
    if('Table'[Index]=max_index,"Last invoice","")

    The PBIX with my generated example is attached you can follow all the steps.

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

     

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    2 years ago

    Hey LostinDaxland ,

     

    create a pbix file that contains sample data. I assume you want to create a calculated column; for this reason, a pbix that only contains sample data for a single table is sufficient. Upload the pbix to OneDrive, Google Drive, or Dropbox and share the link.

     

    Please, explain the expected outcome based on the sample data you provide.

     

    Next I do not understand what's wrong with the formula you have, except that it flags to invoices (Invoice ID) as "Latest Invoice", e.g. project A15?

    If the "Invoice ID" is not sufficient to identify a "single" latest invoice, then no DAX is able to only flag a single row per project.

    If  the invoice id is sufficient a two step process will help

    • find the max date per project
    • use the max date to find the max invoice id per project
    • flag the row where the max invoice id equals the invoice id of the current row, personally I would also leverage the project number in the condition

    Regards,

    Tom