Forum Discussion
Date slicer - different dates
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!
3 Replies
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?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
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?
