Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Asking for help with a Dates Calculated Column

I'm still a pretty new BI user, coming from years of excel experience, so I'm still trying to learn the DAX/excel formula crossover.   I have a dataset of transaction dates, pulling invoice and rec...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

    I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create a calculated column as below:

    INVDATE = 
CALCULATE (
    MAX ( 'Table'[TransactionDate] ),
    FILTER (
        'Table',
        'Table'[InvoiceNumber] = EARLIER ( 'Table'[InvoiceNumber] )
            && 'Table'[TransactionType] = "IN"
    )
)

    If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

    How to upload PBI in Community

    Best Regards

johnt75's avatar
johnt75
Super User
4 years ago
InvDate = IF( 'Table'[Transaction Type] = "IN", 'Table'[Transaction Date],
var invNumber = 'Table'[Invoice Number]
return LOOKUPVALUE( 'Table'[Transaction Date], 'Table'[Invoice Number], invNumber, 'Table'[Transaction Type], "IN")
)
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Thanks for the feedback, johnt75, I think this got me very close. I got an error that multiple values were returned where a single value was expected. I'm thinking this is due to the fact that a single invoice number can show appear mutliple times in the dataset if there were multiple lines invoiced. Any suggestions for working around the duplicates? Thanks!

  • johnt75's avatar
    johnt75
    Super User
    4 years ago

    you could try wrapping the LOOKUPVALUE in DISTINCT()