Use the last date from a slicer to sum all data per client prior to that date

[Anonymous]

I have a Visual Page where I want to show one table that shows the total invoiced per client for the selected dates from a slicer so if I choose July 2020, it returns the invoiced amounts per client for July. This works.

I also want to have a table next to that that shows the sum of the outstanding invoices per client to the end of July (in this case). I have worked out how to show the last selected date in the slicer in a card but I cannot get it to work in the second table. No matter what date I select, it just shows the total outstanding up to November - see below screenshot. You can see in the table on the left it is correctly summing the amounts invoiced in September and the amounts outstanding that have Invoice Dates in September. On the right its just totalling the outstanding invoices. This is the syntax I am using:

UnpaidInvoices =

VAR LastDateFound = LASTDATE(InvoiceData[Invoice Date] )

Return

CALCULATE(

SUM(InvoiceData[Invoice Amount Outstanding (incl tax)]), InvoiceData[Invoice Date].[Date] <= LastDateFound)

This is the syntax that provides the date shown in the card. I have tried putting this code in the above and nothing happens.

LastSelectedDate = FORMAT( (CALCULATE ( MAX ( InvoiceData[Invoice Date] ), ALLSELECTED ( InvoiceData[Invoice Date] ) )), "dd/mm/yyyy")

I have been caught by some formulas not working because its a measure etc etc etc. I have searched high and low and just cannot find a simple solution to find the last date in the slicer and then sum all the data per client from the beginning up to and including that date. Please help. Its driving me insane

[Anonymous]

Hi @Anonymous ,

Could you please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business?

Best Regards,

Stephen Tao

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

[Anonymous]

Hi Stephen

Its a straight forward invoice listing table with the following columns:
Invoice Date
Invoice amount
Paid - Yes or No
Paid Date
Paid Amount
Outstanding Amount

In Excel its very easy to do, just sum all outstanding amounts from StartDate to selected date.

All I want is if I select say August in the time slicer, the Outstanding Table will then show a list of clients and how much they have outstanding at 31 August. I would prefer not to have to use a second date selector for that table as it just takes up more space. I can currently list all invoices in the slicer selected dates but cannot work out how to include invoices prior to the selected dates. I am likely getting the concept of Measure vs Table mixed up or have not found the correct DAX formulas etc

[VijayP]

@Anonymous 

Create a Table with list of invoices and let it interact with only a Month slicer so that you will get list of entries for a specific month and anyhow you have the total of a specific month based on lastday selection. hope this clarifies

[Anonymous]

Thanks Vijay

I have that already. I am trying to sum all outstanding invoices from the beginning to the end of the month selected in the slicer. I would prefer not to have multiple slicers so the client selects say Sept and in one table can see all invoicing for Sept and in the other, all outstanding invoices up to and including Sept. If he selects May-Sept, he should get the same answer as above in the 2nd table