Creating an "Open Transactions" report for Accounts Receivable

[LarsThomas]

Hi all! I am quite new with PoweBI and I am trying to create a report that shows all open (not settled) customer transactions at a given date. The data is based on Microsoft Dynamics D365. Im facing some difficulties and I hope you might be able to help! 

In my table I have lots of transactions with the following data:

Customer number - Transaction ID - Transdate - Amount - Settled date

10                       - 1                    - 31.01.2018 - 1000     - 05.02.2018     (this is not an open transaction)

14                       - 2                    -  31.01.2018 - 500      - BLANK     (The settled date is blank, so this is an open transaction)

10                       - 3                    - 31.01.2018 - 1000    - 20.02.2018     (this is not an open transaction)

I want to be able to filter by date, so that if I chose "from date" 01.01.2018 to "end-date" 31.01.2018, it will return all three transactions (they were all open on the 31.01.2018), but I if chose "end-date" 06.02.2018, it will only return transaction ID 2 and 3.

Im having some difficulties explaining this in a good way, please feel free to ask if there is something that is unclear.

Regards

[RizAbdullah]

Hi, I understand this is very old thread, but can I ask if you happen to know the name od the table from D365 to look at the open transactions please. Many thanks

[v-yuezhe-msft · Accepted solution]

@LarsThomas,

1. Create a date table in Power BI Desktop following the guide in this blog. Please note that there is no relationship between the date table and your original table.

2. Create a column using DAX below in your original table.

newdate = IF(ISBLANK(Table[Settled date]),DATE(9999,12,31),Table[Settled date])

3. Create a measure using DAX below in your original table.

Measure  = IF(MIN('Date'[Date])<=MAX(Table[Transdate])&&MAX('Date'[Date])<=MAX(Table[newdate]),1,0)

4. Create a table visual as shown in the following screenshot, create slicer using date field in the Date table, drag the chkmeasure to visual level filter, and set its value to 1.

Regards,
Lydia

[LarsThomas]

Fantastic, thank you!