making a comparison between two years for Accounts Receivable
Hello, I am trying to make a table which displays how many invoices are still open at the end of each month in PowerBI. I got the task to create a visualisation based on from an ERP software my company is using since they want more control over the visualisation. see following picture: The diagram shows how much is still open at the end of the month Jan, Feb, Mar, and so on during 2 different years. I have created a table that includes Debtor code and name, Invoice date, Payment date, and amount. Then I created a matrix based on the data available: With the following formula: Sum Debits = SUM('DebtorsInvoices’[Amount]) Which displays the total value correctly. For example I take code 100034 and on January. When I try doing it in excel, I see these values amounted to 95.945: However, what I want is the snapshot amount of open invoices during a specific year. For example, I want to see the amount of unpaid invoices during January 2023. In Excel, I would custom filter the invoice date to before 01/02/2023 and payment date either greater than 31/01/2023 or blank which will filter the invoice like this: Which equals to 20.282. How can I achieve this in PowerBI? I tried using the formula: OpenInvoicesByMonth = VAR _EndOfCurrentMonth = MAX('DateTable'[Date]) RETURN CALCULATE( SUM('DebtorsInvoices’[Amount]), FILTER( 'Debiteuren mutaties', [Invoice Date] <= _EndOfCurrentMonth && (ISBLANK([PaymentDate]) || [PaymentDate] > _EndOfCurrentMonth) ) ) But, that formula displays the result only for the following invoices: How can I include the paid invoices as well? Thank you for your assistance, and please bear with me as I am still new to PowerBI.
PowerBI Preset date selection and custom selection with comparison period
Hello, We are trying to make a preset date list that compares the same time period last year, whilst also having the option to use a custom filter with the ability to also use a custom comparison date. For some context we have set up the preset date list and have added a dax calcualtion to show the same time period for the previous year i.e. it will show 01/05/2024 compared to 01/05/2023. However, we can not then get a custom date selection with the option to use an additional calendar for a comparison. We've found loads of videos showing how to do this for a single date, but not including a comparison date. Any help would be hugely appreciated! Thanks in Advance! DataContet
