Calculate day difference between two Date Hierarchies from different tables in Power BI

[TomStaps]

Hi everyone,

I have a modeling question in Power BI related to date calculations across tables.

I’m working with two date fields, both shown as Date Hierarchies, and they are located in two different tables:

• Table A: contains a date field (shown as a Date Hierarchy)

• Table B: contains another date field (also shown as a Date Hierarchy)

The tables are related via a shared key: Case ID (one-to-many / many-to-one relationship).

My goal is to calculate the difference in days between these two dates per individual Case ID (row level).

Is it possible to calculate a row-level day difference between two dates when they are only available as Date Hierarchies in different tables, even if the tables are linked by Case ID?

Thanks in advance!

[Olufemi7 · Accepted solution]

Hello @TomStaps,

Yes, it’s possible to calculate the day difference between two dates that live in different tables, even if they’re shown as Date Hierarchies. The hierarchy is just a visual breakdown — underneath, the field is still a Date column you can use in DAX.

Solution Options

1. Calculated Column (preferred if relationship exists)
If Table A and Table B are related by CaseID:

DayDifference =
DATEDIFF(
TableA[Date],
RELATED(TableB[Date]),
DAY
)

RELATED pulls the corresponding date from Table B into Table A’s row context.
DATEDIFF returns the difference in days.

1. Using LOOKUPVALUE (if no direct relationship)

DayDifference =
DATEDIFF(
TableA[Date],
LOOKUPVALUE(
TableB[Date],
TableB[CaseID], TableA[CaseID]
),
DAY
)

Explicitly matches Case IDs across tables. Useful if the relationship isn’t one-to-many or isn’t defined in the model.

1. Measure (dynamic calculation in visuals)

DayDifferenceMeasure =
DATEDIFF(
SELECTEDVALUE(TableA[Date]),
SELECTEDVALUE(TableB[Date]),
DAY
)

Works when CaseID is in the filter context (e.g., in visuals). Does not store the difference per row, but calculates it dynamically.

Handling Multiple Dates per Case ID
If Table B has multiple dates per Case ID, decide which one to use:

Earliest date:

MinDateB = CALCULATE(MIN(TableB[Date]), TableB[CaseID] = TableA[CaseID])

Latest date:

MaxDateB = CALCULATE(MAX(TableB[Date]), TableB[CaseID] = TableA[CaseID])

Then apply DATEDIFF against MinDateB or MaxDateB.

In Microsoft Fabric
Fabric semantic models use the same DAX engine as Power BI, so the exact same formulas (DATEDIFF, RELATED, LOOKUPVALUE) apply.
If you’re working directly in a Fabric Data Warehouse with SQL, you can use:

SELECT DATEDIFF(DAY, A.Date, B.Date) AS DayDifference
FROM TableA A
JOIN TableB B ON A.CaseID = B.CaseID;

Official Documentation

• DATEDIFF function (DAX): `https://learn.microsoft.com/en-us/dax/datediff-function-dax` 
• RELATED function (DAX): `https://learn.microsoft.com/en-us/dax/related-function-dax` 
• LOOKUPVALUE function (DAX): `https://learn.microsoft.com/en-us/dax/lookupvalue-function-dax` 

Key Notes

• Ensure both fields are Date type (not text).
• Relationships must be correctly defined (one-to-many or many-to-one).
• Decide how to handle multiple dates per Case ID (min, max, or specific filter).

Final Recommendation: Use DATEDIFF with RELATED if the relationship exists. If not, fall back to LOOKUPVALUE. In Fabric, the same DAX works, or you can use SQL DATEDIFF in Warehouse.

[v-ssriganesh]

Hello @TomStaps,

Hope everything’s going great with you. Just checking in has the issue been resolved or are you still running into problems? Sharing an update can really help others facing the same thing.

Thank you.

[Murtaza_Ghafoor]

First of all, you need to decide which date to compare

First date?

Last date?

Min / Max?

You use the base date column

Not Year, Month, or Day from the hierarchy

Use the original Date column, not hierarchies

Use this measure for dynamic calculation

For an example

Days Difference :=

DATEDIFF(

    MIN ( TableA[Created Date] ),

    MIN ( TableB[Closed Date] ),

    DAY

)

Or you can use this measure if you need at calculated column level and if you want to store the value not the dynamic calculation. You need to create a calculated column there


Days Difference =

DATEDIFF(

    TableA[Created Date],

    RELATED ( TableB[Closed Date] ),

    DAY

)

This only works if you have

Table B has one row per Case ID

Relationship is many-to-one

[v-ssriganesh]

Hi @TomStaps,

Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to @cengizhanarslan & @Olufemi7 for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.

Thank you for being part of the Microsoft Fabric Community.

[cengizhanarslan · Accepted solution]

In the Fields pane, expand each hierarchy and identify the original column (or use the date column in the table directly). Then create a measure or calculated column. 

Days Diff =
VAR DateA = SELECTEDVALUE ( 'Table A'[Date] )
VAR DateB = SELECTEDVALUE ( 'Table B'[Date] )
RETURN
IF (
    NOT ISBLANK(DateA) && NOT ISBLANK(DateB),
    DATEDIFF ( DateA, DateB, DAY )
)

This only works if, in the current context, each Case ID has a single DateA and a single DateB.

[Olufemi7 · Accepted solution]

Hello @TomStaps,

Yes, it’s possible to calculate the day difference between two dates that live in different tables, even if they’re shown as Date Hierarchies. The hierarchy is just a visual breakdown — underneath, the field is still a Date column you can use in DAX.

Solution Options

1. Calculated Column (preferred if relationship exists)
If Table A and Table B are related by CaseID:

DayDifference =
DATEDIFF(
TableA[Date],
RELATED(TableB[Date]),
DAY
)

RELATED pulls the corresponding date from Table B into Table A’s row context.
DATEDIFF returns the difference in days.

1. Using LOOKUPVALUE (if no direct relationship)

DayDifference =
DATEDIFF(
TableA[Date],
LOOKUPVALUE(
TableB[Date],
TableB[CaseID], TableA[CaseID]
),
DAY
)

Explicitly matches Case IDs across tables. Useful if the relationship isn’t one-to-many or isn’t defined in the model.

1. Measure (dynamic calculation in visuals)

DayDifferenceMeasure =
DATEDIFF(
SELECTEDVALUE(TableA[Date]),
SELECTEDVALUE(TableB[Date]),
DAY
)

Works when CaseID is in the filter context (e.g., in visuals). Does not store the difference per row, but calculates it dynamically.

Handling Multiple Dates per Case ID
If Table B has multiple dates per Case ID, decide which one to use:

Earliest date:

MinDateB = CALCULATE(MIN(TableB[Date]), TableB[CaseID] = TableA[CaseID])

Latest date:

MaxDateB = CALCULATE(MAX(TableB[Date]), TableB[CaseID] = TableA[CaseID])

Then apply DATEDIFF against MinDateB or MaxDateB.

In Microsoft Fabric
Fabric semantic models use the same DAX engine as Power BI, so the exact same formulas (DATEDIFF, RELATED, LOOKUPVALUE) apply.
If you’re working directly in a Fabric Data Warehouse with SQL, you can use:

SELECT DATEDIFF(DAY, A.Date, B.Date) AS DayDifference
FROM TableA A
JOIN TableB B ON A.CaseID = B.CaseID;

Official Documentation

• DATEDIFF function (DAX): `https://learn.microsoft.com/en-us/dax/datediff-function-dax` 
• RELATED function (DAX): `https://learn.microsoft.com/en-us/dax/related-function-dax` 
• LOOKUPVALUE function (DAX): `https://learn.microsoft.com/en-us/dax/lookupvalue-function-dax` 

Key Notes

• Ensure both fields are Date type (not text).
• Relationships must be correctly defined (one-to-many or many-to-one).
• Decide how to handle multiple dates per Case ID (min, max, or specific filter).

Final Recommendation: Use DATEDIFF with RELATED if the relationship exists. If not, fall back to LOOKUPVALUE. In Fabric, the same DAX works, or you can use SQL DATEDIFF in Warehouse.