Defining overdues towards a dynamic filtered date

[ganchevd]

Dear All,

I had stuck with this. Since I'm a Financial guy not an SQL expert. I have built a report for Account Receivables where I have the following columns:

1. Customer;

2. Document Number;

3. Document date/ Invoice date;

4. Amout;

5. Due date.

Now I put them on a nice chart where I have a slicer to chose for what period of time I'd like to see the overdues. I cannot think of formula to create a dynamic column called "Status" where it shold return "Overdue", "OK". Whenever I change the date filter I want to receive the correct status towards the corresponding date. The data is imported from SQL Database.

I'd be very thankful if someone help me with this dynamic slicer. Thank you all in advance.

[Eric_Zhang · Accepted solution]

@ganchevd

In order to get a dynamic date ,you can create a calendar table with the DAX below:

calendar =
CALENDAR ( MIN ( 'sample'[due date] ), MAX ( 'sample'[due date] ) )

Then create two more measures

selected date =
IF ( HASONEVALUE ( 'calendar'[date] ), MAX ( 'calendar'[date] ), TODAY () )

state =
IF ( 'calendar'[selected date] > MAX ( 'sample'[due date] ), "overdue", "ok" )

[Interkoubess]

Hi @ganchevd,

Please give us sample data ( easy to copy and paste or to manipulate) with your expected outcome and we will try to help.

Thank you.

Ninter

[ganchevd]

Hi,

Here is some sample data

Customer name	Document No.:	Dodument date	Amount	Due date
Private Consult ltd	1000671981	10.08.2017	1 590.00	25.08.2017
Mylander	1000671982	12.08.2017	3 100.00	27.08.2017
TechTrader	1000671983	12.08.2017	120.00	27.08.2017
GanchevSports	1000671984	15.08.2017	4 500.00	30.08.2017
Plan-B JSC	1000671985	20.08.2017	1 000.00	04.09.2017
Truckvaley	1000671986	15.09.2017	300.00	30.09.2017
Autoparts.com	1000671987	20.09.2017	15 000.00	05.10.2017
Auxentrix	1000671988	20.09.2017	7 359.00	05.10.2017

Not sure how to drop it here. Thanks!

Deyan

[Eric_Zhang · Accepted solution]

@ganchevd

In order to get a dynamic date ,you can create a calendar table with the DAX below:

calendar =
CALENDAR ( MIN ( 'sample'[due date] ), MAX ( 'sample'[due date] ) )

Then create two more measures

selected date =
IF ( HASONEVALUE ( 'calendar'[date] ), MAX ( 'calendar'[date] ), TODAY () )

state =
IF ( 'calendar'[selected date] > MAX ( 'sample'[due date] ), "overdue", "ok" )

[ganchevd · Accepted solution]

@Eric_Zhang

Thank you! It seems quite easy. But now I lost the Customers names. When I add the Status column in the visual table, the customers names disapear and the status is either OK or Overdue to all of the entries.