topic Re: DATEDIFF with CALCULATE, USERELATIONSHIP, and TODAY() in DAX Commands and Tips

DATEDIFF with CALCULATE, USERELATIONSHIP, and TODAY()

MuppetyMe — Mon, 16 Dec 2024 19:37:19 GMT

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??

Re: DATEDIFF with CALCULATE, USERELATIONSHIP, and TODAY()

lbendlin — Thu, 12 Dec 2024 20:50:18 GMT

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-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: DATEDIFF with CALCULATE, USERELATIONSHIP, and TODAY()

johnt75 — Fri, 13 Dec 2024 14:18:26 GMT

You can use

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

Re: DATEDIFF with CALCULATE, USERELATIONSHIP, and TODAY()

MuppetyMe — Fri, 13 Dec 2024 21:32:07 GMT

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

Re: DATEDIFF with CALCULATE, USERELATIONSHIP, and TODAY()

MuppetyMe — Fri, 13 Dec 2024 21:51:18 GMT

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

Re: DATEDIFF with CALCULATE, USERELATIONSHIP, and TODAY()

lbendlin — Fri, 13 Dec 2024 22:08:41 GMT

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

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

Re: DATEDIFF with CALCULATE, USERELATIONSHIP, and TODAY()

MuppetyMe — Mon, 16 Dec 2024 18:05:54 GMT

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?

Re: DATEDIFF with CALCULATE, USERELATIONSHIP, and TODAY()

lbendlin — Mon, 16 Dec 2024 19:25:27 GMT

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

Re: DATEDIFF with CALCULATE, USERELATIONSHIP, and TODAY()

MuppetyMe — Mon, 16 Dec 2024 19:30:05 GMT

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

Re: DATEDIFF with CALCULATE, USERELATIONSHIP, and TODAY()

lbendlin — Mon, 16 Dec 2024 19:35:02 GMT

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