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

Hello Anonymous,

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.

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.