two date slicers

[ddtblar]

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

[Anonymous]

@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

[ddtblar]

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)

[ddtblar]

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)

[Stachu]

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/