Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
hey guys,
i need help. I am trying to filter data based on two date slicers.
I have 2 date table for slicer (invoice date table and paid date table) and 2 data table (invoice data and paid data).
I already connect the relationship between those table.
in the report screen, i have matrix table that contain measures from invoice and paid data table.
But those slicers can't filter the data just like i want. Can you guys help me?
thanks
@ddtblar,
Could you please share sample data of your tables and post expected result based on sample data here?
Besides, why not create a calendar table, and then create relationship between calendar table and invoice date table, create relationship between calendar table and paid date table? This way, you can create a slicer using date field of the calendar table to filter visuals.
Regards,
Lydia
I have 2 table just like this:
- Invoice data
CUSTOMER_ID | STORE_ID | REFNBR | AMOUNT | DUEDATE |
M0028 | TP1 | 027842 | 50,000.00 | 15-Apr-18 |
G0061 | TP1 | 34122 | 100,000.00 | 20-Apr-18 |
D0087 | TP2 | 274105 | 80,000.00 | 15-Feb-18 |
M0028 | TP1 | 338465 | 65,000.00 | 01-Jul-18 |
E0001 | TP3 | 25788 | 35,000.00 | 30-Sep-17 |
- Paid data
CUSTOMER_ID | STORE_ID | REFNBR | AMOUNT | PAID_DATE |
M0028 | TP1 | 027842 | 50,000.00 | 30-Apr-18 |
G0061 | TP1 | 34122 | 100,000.00 | 20-May-18 |
D0087 | TP2 | 274105 | 80,000.00 | 16-Apr-18 |
M0028 | TP1 | 338465 | 65,000.00 | 01-Aug-18 |
E0001 | TP3 | 25788 | 35,000.00 | 30-Aug-18 |
I wanna know the AR (account receivable) aging based on the due date. there are 4 categories which are:
- aging 0-30 days
- aging 31-60 days
- aging 61-90 days
- aging more than 90 days
So when the first date slicer is set to 1 august 2018, all duedate invoice transactions for the period of July should be aggregated in a column called 'aging 0-30 days', for duedate in June, which is more than 30 days from 1 august 2018, should be aggregated in a column called 'aging 31-60 days', etc.
In report should show table like this:
AR aging invoice
STOREID | AGING 0-30 | AGING 31-60 | AGING 61-90 | AGING >90 |
TP1 |
|
|
|
|
TP2 |
|
|
|
|
TP3 |
|
|
|
|
For paid table, it is linked with invoice table, so i want to show aging paid table based on duedate in invoice table. Basically the second report table is just like the first table in AR aging invoice, but the categories are follow the duedate, not the paid date. The second slicer in here is use to set the date based on paid date.
Example, the second slicer is set to 31 august 2018. So all data in paid table which more than that is filtered, only the data that below 31 august 2018 are selected. Then, the paid data should be aggregated in a column called 'aging 0-30 days', and other categories based on the invoice just like report table in AR aging invoice
AR aging paid
STOREID | AGING 0-30 | AGING 31-60 | AGING 61-90 | AGING >90 |
TP1 |
|
|
|
|
TP2 |
|
|
|
|
TP3 |
|
|
|
|
I already made two calendar tables, but i cant make this scenario, can you help me?
Thanks. (sorry for my bad english)
hey guys,
I need help about using two date slicer to know the aging of account receivable.
I have 2 table just like this:
- Invoice data
CUSTOMER_ID | STORE_ID | REFNBR | AMOUNT | DUEDATE |
M0028 | TP1 | 027842 | 50,000.00 | 15-Apr-18 |
G0061 | TP1 | 34122 | 100,000.00 | 20-Apr-18 |
D0087 | TP2 | 274105 | 80,000.00 | 15-Feb-18 |
M0028 | TP1 | 338465 | 65,000.00 | 01-Jul-18 |
E0001 | TP3 | 25788 | 35,000.00 | 30-Sep-17 |
- Paid data
CUSTOMER_ID | STORE_ID | REFNBR | AMOUNT | PAID_DATE |
M0028 | TP1 | 027842 | 50,000.00 | 30-Apr-18 |
G0061 | TP1 | 34122 | 100,000.00 | 20-May-18 |
D0087 | TP2 | 274105 | 80,000.00 | 16-Apr-18 |
M0028 | TP1 | 338465 | 65,000.00 | 01-Aug-18 |
E0001 | TP3 | 25788 | 35,000.00 | 30-Aug-18 |
I wanna know the AR (account receivable) aging based on the due date. there are 4 categories which are:
- aging 0-30 days
- aging 31-60 days
- aging 61-90 days
- aging more than 90 days
So when the first date slicer is set to 1 august 2018, all duedate invoice transactions for the period of July should be aggregated in a column called 'aging 0-30 days', for duedate in June, which is more than 30 days from 1 august 2018, should be aggregated in a column called 'aging 31-60 days', etc.
In report should show table like this:
AR aging invoice
STOREID | AGING 0-30 | AGING 31-60 | AGING 61-90 | AGING >90 |
TP1 |
|
|
|
|
TP2 |
|
|
|
|
TP3 |
|
|
|
|
For paid table, it is linked with invoice table, so i want to show aging paid table based on duedate in invoice table. Basically the second report table is just like the first table in AR aging invoice, but the categories are follow the duedate, not the paid date. The second slicer in here is use to set the date based on paid date.
Example, the second slicer is set to 31 august 2018. So all data in paid table which more than that is filtered, only the data that below 31 august 2018 are selected. Then, the paid data should be aggregated in a column called 'aging 0-30 days', and other categories based on the invoice just like report table in AR aging invoice
AR aging paid
STOREID | AGING 0-30 | AGING 31-60 | AGING 61-90 | AGING >90 |
TP1 |
|
|
|
|
TP2 |
|
|
|
|
TP3 |
|
|
|
|
I already made two calendar tables, but i cant make this scenario, can you help me?
Thanks. (sorry for my bad english)
what is the way that you want the slicers to apply?
by default the operator between the slicers is AND, you can overwrite it in DAX with OR equivalent
https://www.sqlbi.com/articles/using-or-conditions-between-slicers-in-dax/
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |