Difference between two dates and using additional filters together with all except

[musafasih]

In my debtors table, there are many customers with multiple transactions. Each transaction has a unique code, say 001,and comprises of 2 to 3 types of transactions (which is a separate column), usually invoices and cash received. These two types of transactions (falling within the same transaction code) will have either same or different dates, with both dates represented in the date column.

Now, my goal is to calculate the difference between invoice date and Cash received date for that invoice. For this, I have used 'all except' function, where I am using transaction code as a filter. this helps to calculate difference between first and last transaction date, though the assumption here is that first transaction is always 'invoice' and last transaction 'cash received'. This assumption holds true for majority of transactions, but not all. Based on this assumption, I have managed to calculate difference between two dates of the same transaction, but I want to use the filter where transaction are only either Invoice or Cash received, and ignore all other transaction types.

Also, I want to use another filter, where certain transaction codes are omitted from my analysis, such as blank codes. I have created two measures, first date and last date, which makes use of all except function, where transactions are grouped by transaction code and first and last date determined. However, I am not able to use additional filters here. Please help.

[Greg_Deckler]

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Fowmy]

@musafasih 

Can you share some sample data and the expected result to have a clear understanding of your question?

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[musafasih]

https://drive.google.com/file/d/13ZjiJ6gWGvL33Z1J8XTkdu9XprnnZ6OP/view?usp=sharing

@Fowmy  Here is a link to the dummy data in MS Excel which I have created. The original data is company data so Im not allowd to share it. So basically what I am trying to do is to find out the difference  (in PowerBI) between first and last date of each transaction code. My ultimate aim is to find out the difference between the date an invoice was issued to customers and the date at which the customers paid for that invoice. The goal is to find out the average days customers take to pay. I had earlier used 'all except' function to group the transactions according to the transaction code and then find out first and last date for each transaction code, but I want to omit the following: ref no with CR BATCH entry; transaction types excluding IN and CR. Also, if I can somehow write a dax formula that will only calculate the difference in days where the first entry is invoice(IN) and last cash received (CR) that would be great. Thank you.

Kind regards,

Musa

[musafasih]

HI @Fowmy ,

How would you like me to share sample data file with you?

Kind regards,

Musa

[Fowmy]

@musafasih 

Either you can save your files in One Drive, Google Drive, or any other cloud sharing platform then share the link in your reply here or you can paste right in the reply box.

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn