DATEDIFF with CALCULATE, USERELATIONSHIP, and TODAY()

[MuppetyMe]

I have 2 tables; a Calendar table (containing a full, continuous range of dates from 1/1/2020 to 12/31/2027) and a Data table. There are multiple inactive relationships between the two as my Data table contains several different dates. 

The Data table contains a "Create Date", and am trying to figure out the age of an item via the difference between TODAY() and Create Date. 

In my mind, the measure should be pretty straigh forward: Age = CALCULATE (DATEDIFF (USERELATIONSHIP ('Calendar' [date], 'Data' [Create Date]), TODAY(), DAY)), but it does not work. Thoughts or suggestions?? 

[MuppetyMe]

@lbendlin this is obviously completely bogus data, but gives the basic gist. The data table has dozens of additional columns and thousands of rows. Each of the date fields have indirect connections to the Calendar table. Ultimately, I need to be able to create a date filter using the Calendar table. 

I understand that the simplest solution would be to create 4 different date filters using data in the table, but that's not what we're aiming to do. Hopefully this helps to explain what I'm after. 

Data

Reference	Create Date	Date1	Date2	Date3
1	4/21/2021	5/3/2021	5/12/2021	5/21/2021
2	9/17/2021	9/29/2021	10/8/2021	10/17/2021
3	6/12/2022	6/24/2022	7/3/2022	7/12/2022
4	10/10/2022	10/22/2022	10/31/2022	11/9/2022
5	1/4/2023	1/16/2023	1/25/2023	2/3/2023
6	3/23/2023	4/4/2023	4/13/2023	4/22/2023
7	5/29/2024	6/10/2024	6/19/2024	6/28/2024
8	8/19/2024	8/31/2024	9/9/2024	9/18/2024

Calendar

Date

4/21/2021

5/3/2021

5/12/2021

5/21/2021

9/17/2021

9/29/2021

10/8/2021

10/17/2021

6/12/2022

6/24/2022

7/3/2022

7/12/2022

10/10/2022

10/22/2022

10/31/2022

11/9/2022

1/4/2023

1/16/2023

1/25/2023

2/3/2023

3/23/2023

4/4/2023

4/13/2023

4/22/2023

5/29/2024

6/10/2024

6/19/2024

6/28/2024

8/19/2024

8/31/2024

9/9/2024

9/18/2024

[lbendlin]

Your calendar must be contiguous and covering.  No gaps allowed.

You don't need a calendar if you are operating with TODAY() though.

[MuppetyMe]

Busted. The calendar table is contiguous and covering in the real dataset - thought I could cut corners for the example, but not the case. So that being said, @lbendlin, any other thoughts on how I might be able to accomplish? 

[lbendlin]

I showed the option without calendar. Please clarify what else you need.

[MuppetyMe]

Looking for the solution WITH a contiguous and covering calendar table. 

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[johnt75]

You can use

Age =
SUMX ( 'Data', DATEDIFF ( 'Data'[Create date], TODAY (), DAY ) )

[MuppetyMe]

Thanks @johnt75, this does work, but unfortunately not the way I need it to.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...