Forum Discussion
Defining overdues towards a dynamic filtered date
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.
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" )
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.
4 Replies
- Interkoubess
Solution Sage
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
Helper I
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
Microsoft Employee
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" )