Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ddtblar
Helper I
Helper I

two date slicers

hey guys, 

i need help. I am trying to filter data based on two date slicers.

2 slicers.PNG

 

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

 

 

4 REPLIES 4
v-yuezhe-msft
Employee
Employee

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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)

 

Stachu
Community Champion
Community Champion

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/



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.