Create measure that returns "in N days" or "N days ago" using inactive relationship to date table

[carinalou]

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!

[barritown · Accepted 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

My YouTube vlog in English

My YouTube vlog in Russian

[carinalou]

Thanks for your help!