Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
vandelD
New Member

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 receipt data, identified by the TransType of IN (invoice) and CR (cash receipt). I'm wanting to create a calculated column that shows me the invoice date, because my data set ONLY has the transaction date.

 

What I would expect in excel would be an IF formula with a nested INDEX to lookup the transaction date of the Invoice line: "IF(TransType = "IN",TransDate,INDEX(TransDate,MATCH(invoice218978,Invoice,0))). Not having the Index-Match option in BI is hurting me. Basically what I'm wanting is, if the transaction is "IN", make the calculated column InvDate equal the TransDate of that line. If it's a "CR" transaction, lookup the invoice number from within the dataset and pull back the TransDate from the corresponding "IN" transaction.

 

Here's an example of the dataset that I have:

TransactionTypeTransactionDateTransactionAmountInvoiceNumberINVDATE
IN2/10/20224025218978=inv218978 TransType IN TransDate
CR4/18/20224025218978=inv218978 TransType IN TransDate
IN2/15/20221926219105=inv219105 TransType IN TransDate
IN2/15/20226854219106=inv219106 TransType IN TransDate
CR4/19/20221926219105=inv219105 TransType IN TransDate

 

Any insight on this, even if it's to tell me I'm way off would be extremely appreciated!

1 ACCEPTED SOLUTION
v-yiruan-msft
Community Support
Community Support

Hi @vandelD ,

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"
    )
)

yingyinr_0-1653639700427.png

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

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

View solution in original post

5 REPLIES 5
v-yiruan-msft
Community Support
Community Support

Hi @vandelD ,

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"
    )
)

yingyinr_0-1653639700427.png

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

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

This seems to work, thank you so much for the help!

johnt75
Super User
Super User

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")
)

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!

you could try wrapping the LOOKUPVALUE in DISTINCT()

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors