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.
I am trying to create a table visual that shows upcoming due dates of trainings assigned to employees.
My fact table of trainings assigned looks like this:
Training | Employee | Assigned Date | Due Date | Start Date | Completed Date |
Training 1 | Employee A | 10/1/202 3 | 10/31/2023 | 10/3/2023 | 10/3/2023 |
Training 1 | Employee B | 10/1/2023 | 10/31/2023 | 10/7/2023 | 10/12/2023 |
Training 1 | Employee C | 10/1/2023 | 10/31/2023 | 10/31/2023 | |
Training 2 | Employee A | 10/15/2023 | 11/14/2023 | 10/16/2023 | 10/17/2023 |
Training 2 | Employee B | 10/15/2023 | 11/14/2023 | 10/19/2023 | |
Training 2 | Employee C | 10/15/2023 | 11/14/2023 | ||
Training 3 | Employee A | 11/1/2023 | 11/30/2023 | 11/2/2023 | |
Training 3 | Employee B | 11/1/2023 | 11/30/2023 | ||
Training 3 | Employee C | 11/1/2023 | 11/30/2023 |
All 4 date fields have a relationship to the date table, with Assigned Date being the active one. Here is the date table:
Date | Days From Today |
10/1/2023 | 38 |
10/2/2023 | 37 |
10/3/2023 | 36 |
10/4/2023 | 35 |
10/5/2023 | 34 |
10/6/2023 | 33 |
10/7/2023 | 32 |
10/8/2023 | 31 |
10/9/2023 | 30 |
10/10/2023 | 29 |
I want to create a table visual that looks like this:
Due | Training | % Complete |
8 days ago | Training 1 | 67% |
in 7 days | Training 2 | 33% |
in 22 days | Training 3 | 0% |
How can I write a DAX measure to use the inactive relationship between Due Date and the date table, and return "N days ago" for the past and "in N days" for the future? Thanks in advance!
Solved! Go to Solution.
Hi @carinalou,
If it is a mandatory thing to use the inactive relationship and the column "Days From Today" then I'd enable filtering in both directions for this inactive relationship and use the measure below.
Otherwise, I'd do the same with only the Fact table and DAX TODAY() function.
Please check the attached file for more details.
Best Regards,
Alexander
Hi @carinalou,
If it is a mandatory thing to use the inactive relationship and the column "Days From Today" then I'd enable filtering in both directions for this inactive relationship and use the measure below.
Otherwise, I'd do the same with only the Fact table and DAX TODAY() function.
Please check the attached file for more details.
Best Regards,
Alexander
Thanks for your help!
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 |
---|---|
100 | |
100 | |
76 | |
68 | |
61 |
User | Count |
---|---|
142 | |
106 | |
103 | |
85 | |
70 |