Measure (or Calculated Column) to Restate Second Measure

[ExcelingInLife]

Thanks in advance. Title may be confusing but essentially I'm looking to calculate days past due. In power pivot I am taking Today()-Due Date to calculate the days. However, I want to be able to look at it historically. Any thoughts how I could connect a selected value from the calendar it is connected to (call it Calendar[Date])? Essentially if select 9/1/2019 I want to do 9/1/2019 - Due Date.

 

[JustynaMK]

Hi,

You won't be able to use a Calculated Column as these do not respond to the slicers or selections.

As for the measure, you can try the following formula -

Date Diff = 
    DATEDIFF(MAX('Calendar'[Date]), MAX(tbl_DueDate[Due Date]), DAY)

Here's an exemplary result -

 

[ExcelingInLife]

Hi,

You won't be able to use a Calculated Column as these do not respond to the slicers or selections.

As for the measure, you can try the following formula -

Date Diff =
    DATEDIFF(MAX('Calendar'[Date]), MAX(tbl_DueDate[Due Date]), DAY)

Here's an exemplary result -
View attachment 13783

Appreciate the help. No luck on this. Perhaps because my calendar is built to be from 2016-2024 for flexibility? Tried it with selectedvalue but no luck either

 

[JustynaMK]

Thanks for the update - the calendar range shouldn't be the issue.

Could you do me a favor and add the measure to your table, but split it into two parts -
First, test what is the result of Date Diff = MAX('Calendar'[Date]) - does it always return the selected Calendar Date for each row?
Second, test what is the result of Date Diff = MAX(tbl_DueDate[Due Date]) - does it always return the same date as "Due Date" column in the same table?

Any screenshots you can provide will be also helpful.