Forum Discussion
ganchevd
Helper I
Helper IDefining 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; ...
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.
ganchevdHelper I
Helper IHi,
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
Microsoft EmployeeIn 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
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.