Date slicer - different dates

[LarsThomas]

Hi everybody, PowerBI newbie here. 

I have a table that includes customer transactions, including invoicenumber, invoice date and settlement date (if settled).

Example:

Invoice       InvoiceDate       Amount           Settleddate

444            20/02/2018        200                15/03/2018

555            23/02/2018        400                15/03/2018

666            13/02/2018        650                29/04/2018

777            15/04/2018        300                0/01/1900 (unsettled)

1. If set my date from 01/01/2018 to 31/03/2018 - I want it to return invoice 444,555 and 666 (777 has not been invoiced yet)

2. If I set my date from 01/02/2018 to 28/02/2018 - I want it to return 444, 555 and 666 (777 has not been invoiced yet)

3. If I set my date from 01/01/2018 to 30/04/2018 - I want it to return all invoices

4. If I set my date from 01/04/2018 to 01/05"018 - I want it to return only 777 (444, 555 and 666 is outside of my date intervall).

Thank you!

[danextian]

Hi @LarsThomas,

I'm quite confused with what you are trying to achieve. Wouldn't selecting dates from the slicer natually include/exclude items? Wouldn't it be more helpful to add a calculated column that will indicate the status of your invoices and just use the date slicers to identify which invoices were invoiced/settled within a date range?

[Sandeep641]

You can use date table . connect one of the date to the date table (active connection) and make the other inactive. You can use calculate(sum(amount)userelationship(invoicedate, datetable)

This should work 

[PaulDBrown]

@LarsThomas

Hi Lars,

When you say you want the output of your date selection to be

"I want it to return invoice 444,555 and 666 (777 has not been invoiced yet)" etc...,

what are your referring to exactly? you mean you want the invoice numbers listed? you want the table to be filtered? you want the SUM of the amounts filtered by the date selection?

If you have a table as a visual, have you tried adding a slicer to the page with your dates as the field in the slicer?