Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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.
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
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |